Oracle – Select Strings with Line Breaks

oracleoracle-12c

How can I select strings that have line breaks in them?

I can think of these possibilities when it comes to line breaks in strings:

create table test1 (
    id number(4,0),
    f1 varchar(25));

insert into test1(id,f1) values (1,'a');
insert into test1(id,f1) values (2,chr(10) || 'a');
insert into test1(id,f1) values (3,'a' || chr(10));
insert into test1(id,f1) values (4,'a' || chr(10) || 'a');
insert into test1(id,f1) values (5,'a' || chr(10) || chr(10) || 'a');
insert into test1(id,f1) values (6,chr(10));
commit;

chr(10) is the value for line breaks.

There might be more possibilities that I haven't thought of.

Best Answer

Don't try to ask the result of dump() if there is a newline character in the string. Ask the string directly.

Example where clause

where f1 like '%' || chr(10) || '%'

or

where instr(f1,chr(10)) > 0

or (Per John A)

where regexp_like( f1, chr(10), 'n' )
Related Question