Removing Carriage returns from SQL
Posted on 2009-12-30
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.
SET thecolumn = REPLACE(thecolumn ,CHAR(10),'')
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
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