CODEWITHSUNDEEP
sqlms-access
JamesM
JamesM

Reputation:

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

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.

Upvotes: 1

Views: 850

Answers (2)

IordanTanev
IordanTanev

Reputation: 6240

You can use this query to replace newline with empty space:

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

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300589

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), ' ')

Upvotes: 1

Related Questions