Importing a table from Access into SQL and removing the carriage returns

Question

I'm importing a table from Access that has an address field the address field contains carriage returns, these remain when I have imported them into SQL, can I run a SQL script to remove them? Any ideas much appreciated.

Thanks

Answer 1

This will replace Carriage Returns (char(13)) with a single space:

UPDATE MyTable
SET MyColumn = Replace(MyColumn, CHAR(13), ' ')

If you have Carriage Return + LineFeeds:

UPDATE MyTable
SET MyColumn = Replace(MyColumn, CHAR(13)+CHAR(10), ' ')

Answer 2

Hi, you can use this query to replace newline with empty space

replace ( replace(TextValue, char(10),' '), char(13), ' ')

Best Regards, Iordan