Search This Blog

Tuesday, July 13, 2010

Effective Way to Remove CR LF in MySQL Query

I had a need to replace new lines in a query that was causing issues elsewhere in a process flow that I was working on. While removing the new lines in code would be easy, the problem was that they were needed to designate the end of a line in a file. So, I decided to remove them at the earliest possible step, and that is at the query. So, to do so, here is the statement...

REPLACE(REPLACE(userEnteredField, CHAR(10), ''), CHAR(13), '')

Since we are not sure if the input is inputing the new line as Carriage Return, Line Feed, or both, you first replace one, then replace the other. Most solutions I see posted have REPLACE(userEnteredField, CHAR(10) + CHAR(13)) which works if they are both present. The way done replaces, one or the other or both.