Search code examples
mysqlsqlline-breakscarriage-return

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.

Solution

  • 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'