asked on

Removing Carriage returns from SQL

Hey guys this question have already been asked before with what appears to have a very valid answer.
I have a SQL table with data that was converted from a pdf then to excel then csv which i then imported in to SQL and i have since found out that like 70% of all the data have carriage returns after all entries. These carriage are now proving a pain in the backside when searching for data. I founda previous article on here where the solution provided i below.

"
The following SQL is what you need to replace a line feed. char(13) is a carriage return.

UPDATE TableName
SET thecolumn = REPLACE(thecolumn ,CHAR(10),'')
FROM TableName
WHERE patindex(char(10),thecolumn )>0

You'll want to use the INFORMATION_SCHEMA.COLUMNS table to loop through and generate the SQL to create the above update statement for each character datatype column for every table.

This will return all tables and columns that are character data types.
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('char','varchar','nvarchar','text')
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'IsTable') = 1"
Credit to Atlanta_Mike

But whenever i try change the 1st lot of code to suit my database details it returns the following error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM hauliers WHERE patindex(char(10),haulier )>0' at line 3

Any Ideas will be greatly appreciacted

Bill Prew

If you are doing this in MySQL try this:

WHERE locate(char(10),thecolumn )>0

~bp

lammy82

You're using mySQL, not MS SQL Server, so you can't use PATINDEX. You can leave the last line off that query as it doesn't actually make any difference anyway.

So just try using the following, changing the table and column names:

UPDATE TableName
SET thecolumn = REPLACE(thecolumn ,CHAR(10),'')
FROM TableName

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

davids355

ASKER

sorry the example i used wasnt very clear but i ws replacing the char(10) with char(13) like below
i have re-editted it all to

UPDATE hauliers
SET thecolumn = REPLACE(thecolumn ,CHAR(13),'')
FROM hauliers
WHERE locate(char(13),hauliers )>0

but is still seems to fail on the mention of 'FROM'

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM hauliers WHERE locate(char(13),hauliers )>0' at line 3 ?

davids355

ASKER

Sorry and i did change the "thecolumn" parts to my own aswell

Bill Prew

Sorry, didn't see the forest for the trees. Remove the FROM HAULIERS, that doesn't belong in the UPDATE.

http://dev.mysql.com/doc/refman/5.0/en/update.html

~bp

SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

davids355

ASKER

ok i got that code to run but when i open the entries there still appears to be enter presses in them? i have tried another piece of code that removes white spaces from the left and right of the entries but that still didnt seem to do anything. Is there anything else i could try?

awking00

The attached adds the third argument to the replace function (although it should have defaulted to null). What happens when you run it?
comments.txt