CODEWITHSUNDEEP
mysqlsqlline-breakscarriage-return
Jason Youk
Jason Youk

Reputation: 802

How to remove carriage returns and line feeds from a column?

I'm trying to remove carriage returns and line feeds from a column I imported from a csv file.
I'm using the code:

SELECT replace(replace(column,CHAR(13),''),CHAR(10),'')
FROM table

It correctly finds all the CR and LF, but it doesn't update the database.

Upvotes: 6

Views: 27451

Answers (1)

Joe Taras
Joe Taras

Reputation: 15379

Your query retrieve from your table named TABLE all rows with the column replaced.

About UPDATE your database you must use UPDATE command in this way:

UPDATE table SET column = replace(replace(column,CHAR(13),''),CHAR(10),'')

If you want condition the UPDATE about the satisfaction of some conditions, so you must add the WHERE clause.

For example

UPDATE table SET column = replace(replace(column,CHAR(13),''),CHAR(10),'')
WHERE column_2 = 'XXX'

Upvotes: 9

Related Questions