CODEWITHSUNDEEP
sqloracle-database
b.roth
b.roth

Reputation: 9542

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

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: 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?

Upvotes: 3

Views: 7945

Answers (5)

Juergen Hartelt
Juergen Hartelt

Reputation: 674

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

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

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52386

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

Upvotes: 3

tog22
tog22

Reputation: 526

Alternatively:

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

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

Upvotes: -1

dpbradley
dpbradley

Reputation: 11925

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

Upvotes: 0

Vincent Malgrat
Vincent Malgrat

Reputation: 67752

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

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

Upvotes: 3

Related Questions