[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 736
  • Last Modified:

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
0
davids355
Asked:
davids355
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
Bill PrewCommented:
If you are doing this in MySQL try this:

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

~bp
0
 
lammy82Commented:
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

0
 
Bill PrewCommented:
I was also confused, you seem to indicate your problem is carraige returns char(13) in the data, but your sample script looks ofr and removes line feeds char(10)?

~bp
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
davids355Author Commented:
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 ?

0
 
davids355Author Commented:
Sorry and i did change the "thecolumn" parts to my own aswell
0
 
Bill PrewCommented:
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
0
 
awking00Commented:
See attached.
comments.txt
0
 
davids355Author Commented:
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?
0
 
awking00Commented:
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
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now