Replace carriage returns

[rafeman]

Hi All,

I have an address field with data such as

Sheldruck House : Billericay : Essex

I want to replace the ':' with carriage returns.

I'm sure this is simple but how do I do it?

I've tried

update PL_ACCOUNTS
set SUADDRESS = REPLACE(':',char(10),CHAR(13))

But its not correct.

Thanks

 

[bborissov]

update PL_ACCOUNTS
set SUADDRESS = REPLACE(':',char(10)+CHAR(13))

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[bborissov]

OOPS,
CRLF is CHAR(13)+CHAR(10), not CHAR(10)+CHAR(13)
So:

update PL_ACCOUNTS
set SUADDRESS = REPLACE(':',CHAR(13)+CHAR(10))

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Olaf Doschke]

If you already ran your own UPDATE, all your SUADRESSES now will be ':' only. You lost your data. Hopefully you have a backup. The result of REPLACE(':',CHAR(10),CHAR(13) is always ':' only, and you put that into all you SUADDRESS fields.


Parameterisation of REPLACE is this way
1. what to use as initial value (SUADDRESS)
2. what to search (':')
3. what to replace it with (CHAR(13)+CHAR(10))

=> repolkaces ':' within SUADDRESS with CRLF.

Restore the data, then do:

update PL_ACCOUNTS
set SUADDRESS = REPLACE(SUADDRESS,':',Char(13)+CHAR(10))

Bye, Olaf.

 

[Olaf Doschke]

repolkaces -> replaces

(Don't ask me about my ring size.)

Bye, Olaf.

 

[rafeman]

Brilliant, worked a treat. Thanks to you both