Select rows with line breaks: Unexpected result 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');   --&lt;-- 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.
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.