SQL query for a carriage return in a string and ultimately removing carriage return

Question

SQL query for a carriage return in a string and ultimately removing carriage return

I have some data in a table and there are some carriage returns in places where I don't want them. I am trying to write a query to get all of the strings that contain carriage returns.

I tried this

select * from Parameters where Name LIKE '%"\n" %' 

Also

select * from Parameters where Name LIKE '\r' 

'

Both are valid SQL but are not returning what I am looking for. Do I need to use the Like command or a different command? How do I get the carriage return into the query?

The carriage return is not necessarily at the end of the line either (may be in the middle).

Answer 1

this will be slow, but if it is a one time thing, try...

select * from parameters where name like '%'+char(13)+'%' or name like '%'+char(10)+'%' 

Note that the ANSI SQL string concatenation operator is "||", so it may need to be:

select * from parameters where name like '%' || char(13) || '%' or name like '%' || char(10) || '%' 

Answer 2

The main question was to remove the CR/LF. Using the replace and char functions works for me:

Select replace(replace(Name,char(10),''),char(13),'') 

For Postgres or Oracle SQL, use the CHR function instead:

       replace(replace(Name,CHR(10),''),CHR(13),'')