0

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.

2

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

Dump():

DUMP returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The returned result is always in the database character set.

select 
    id,
    f1,
    dump(f1)
from 
    test1 
where 
    dump(f1) like '%: 10,%'    --Selects #2
    or dump(f1) like '%,10'    --Selects #3
    or dump(f1) like '%,10,%'  --Selects #4,5
    or dump(f1) like '%: 10'   --Selects #6
order by
    id

All rows with line breaks are selected. Row #1 is not selected, because it does not have a line break.

enter image description here

  • This should also work: select id, f1 from test1 where regexp_like(f1,CHR(10),'n'); – John A Aug 27 '18 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.