Delete Carriage Returns

[GKWilly]

I have a table that stores addresses in one field with a carriage return between each line.
Unfortuantly some users have been inputing addresses and hitting carrigage returns more than once, hence I have blank lines of data stored.
Is there a way I can delete the unwanted carriage returns and keep the ones that legitimately exist?

Many thanks in advance

 

[aolb]

carrage return is char(10) i think.
Line feed is char(13) i think. I'm sad if I right.

Sounds like you need to fo a find and replace.

REPLACE ( your_field, char(10) + char(10), char(10))

No guarantees

 

[hmckillop]

try

Update MyTable
Set MyColumn1 = REPLACE ( MyColumn1 , CHAR(13) + CHAR(13) , CHAR(13))

obviously replace tablename and column name with your table name and column name.

It should work.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

aolb
I think its the other way round, char(13) is carriage return and char(10) is line feed
so not that sad afterall!

"I'm living so far beyond my income that we may almost be said to be living apart

 

[GKWilly]

many thanks to both of you )