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

Omit the double quotes from your first query.

... LIKE '%\n%'

Answer 2

Something like SELECT * FROM Parameters WHERE Name LIKE '%\n%' seems to work for me.

Answer 3

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)+'%'

Answer 4

You can also use regular expressions:

SELECT * FROM Parameters WHERE Name REGEXP '\n';

Answer 5

In SQL Server I Would use

where charindex(char(13), name)<>0