How to remove newline, carriage return, line feed from an MySQL table field data


SQL to select a field by removing carriage return and line feed from a MySQL table column

 

SELECT REPLACE(REPLACE(field_name, '\r', ''), '\n', '') FROM table_name;

the above query removes all occurrencs of carriage return and line feed from the field data. The following query removes only training carriage return and line feed.

 

SELECT TRIM(TRAILING '\r\n' FROM field_name) FROM table_name;

 

Example : 

SELECT REPLACE(REPLACE(word, '\r', ''), '\n', '') FROM thesaurus;
SELECT TRIM(TRAILING '\r\n' FROM word) FROM thesaurus;

 

SQL to update a field jto remove carriage return and line feed from a MySQL table column

 

 UPDATE table_name SET field_name = REPLACE(REPLACE(field_name, '\r', ''), '\n', '');

Example :

 UPDATE thesaurus SET word = REPLACE(REPLACE(word, '\r', ''), '\n', '');

 

 


© 2010 www.generalconnection.com. All rights reserved. | www.generalconnection.com