My problem is I have an address field where the address has been inputed with a Carriage Return (CR) at the end of each line. I need to update this field and change every instance of a CR to a comma as I am to then separate each line of address to separate fields.

Can anybody point me in the right direction to get SQL to recognise for eg the ASCII(013) = CR.

Sample things I have tried are:

Declare @var1
Declare @var2

Set @var1 = ASCII(043)
Set @var2 = ASCII(033)
SELECT REPLACE ('This string!+','@var1','@var2&#39

OR

SELECT <field> from <table> where <field> like &#39;%ASCII(044)%&#39;

In the above SQL analyser is picking up the characters inside the quote marks literally and not as ASCII code which is what I really want.

Any help at all would be greatly appreciated.
Thanks!