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

sql char carriage-return sql-like
Maestro1024 picture Maestro1024 · Aug 26, 2009 · Viewed 243.6k times · Source

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

KM. picture KM. · Aug 26, 2009

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

Related questions

What is the major difference between Varchar2 and char

Creating Table: CREATE TABLE test ( charcol CHAR(10), varcharcol VARCHAR2(10)); SELECT LENGTH(charcol), LENGTH(varcharcol) FROM test; Result: LENGTH(CHARCOL) LENGTH(VARCHARCOL) --------------- ------------------ 10 1 Please Let me know what is the difference between Varchar2 and char? At what times we use …

Read More
SQL ORDER chars numerically

I have a column of numbers stored as chars. When I do a ORDER BY for this column I get the following: 100 131 200 21 30 31000 etc. How can I order these chars numerically? Do I need to convert something or is there already …

Read More
How to count the number of times a character appears in a SQL column?

For a user logging table I have in a SQL database, I track the some of the parameters off of a report request. The report allows multiple ID's to be passed to it and I store all of those in …

Read More