In Oracle, what is the SQL to query for occurences of line feed?

Question

I need to find the rows where a certain column contains line feed.

This does not work: select * from [table] where [column] like '%\n%'

In SO, I found the solution for SQL Server: http://stackoverflow.com/questions/1085662/new-line-in-sql-query

But this does not work in Oracle. Is there any ANSI SQL solution? This should be a standard...

If not, what is the solution in Oracle?

Answer 1

Hi Bruno,

you could look for the CHR(10) character (the character for newline):

select * from [table] where instr(column, chr(10)) > 0

Answer 2

select * from tableNameHere where instr(colNameHere, chr(10)) > 0

Answer 3

Alternatively:

SELECT * FROM [table] WHERE column LIKE "%\n%"

\n is line feed, \r is carriage return...

Answer 4

An alternative to InStr() that expresses the SQL a bit more in line with the problem. IMHO.

select * from [table] where [column] like '%'||chr(10)||'%'

Answer 5

If you are working with Oracle 10g upwards, you could use

select * from [table] where regexp_like([column], '\n')