0

I'm trying to find rows in a table where there are line breaks.

I found some sample code that lets me do this:

where dump(f1) like '%,10%' and Not (dump(f1) like '%100%')


However, I'm getting an unexpected result.

create table test1 (f1 varchar(25));

insert into test1(f1) values ('Note');
insert into test1(f1) values ('Nota');   --<-- Slight spelling difference

select * from test1 where dump(f1) like '%,10%' and  Not (dump(f1) like '%100%')

F1                       
-------------------------
Note

Why does the query return a record, if there isn't a carriage return? And why does it only return a single record?

Prediction: I'm having a brain fart and/or I have no idea what I'm doing. It is Monday morning, after all.

1

DUMP returns a VARCHAR2.

select f1, dump(f1) from test1;

F1            DUMP(F1)
------------- ---------------------------- 
Note          Typ=1 Len=4: 78,111,116,101
Nota          Typ=1 Len=4: 78,111,116,97

2 rows selected.

So searching for '%,10%' will only return 'Note' because it's dump representation contains 101, where as 'Nota' does not.

If you insert those values with a return character/line break, the result would be:

select  f1, dump(f1)
from test1 where 
dump(f1) like '%,10%' 
and  Not (dump(f1) like '%100%');

F1            DUMP(F1)
------------  -----------------------------------------------
Note          Typ=1 Len=4: 78,111,116,101
Note          Typ=1 Len=5: 78,111,116,101,10
Nota          Typ=1 Len=5: 78,111,116,97,10

You get 3 rows returned. The 2 rows ending with ',10' contain the the line breaks.

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.