SQL and embedded carriage returns

[henrybluebottle]

I wonder if anyone knows how to search a column in SQL for embedded carriage returns ? I assume it would be a "like '%...%' " but do I use a chr(13) or /n or what ? It's Oracle BTW.

thanks,
HB.

[mixture]

henrybluebottle,

Oracle is cool ! Don't let the open-source naysayers tell you MySQL can do the same things... its an awesome, powerful product ! Plus there's no excuse about the money element, since XE is free !

But I digress.... here's one example....

Oracle Database 11g 11.2.0.2.0 - 64bit Production


create table mytest(idx number(1,0),mycol varchar2(5));
insert into mytest(idx,mycol) values ('1','a');
insert into mytest(idx,mycol) values ('2','b'||chr(13));

select dump(mycol,10) from mytest;
DUMP(MYCOL,10)
-----------------------------------------
Typ=1 Len=1: 97
Typ=1 Len=2: 98,13


select idx from mytest where substr(mycol,2,1)=chr(13);

IDX
----------
2


Or another....


select idx from mytest where regexp_like(mycol,chr(13));

IDX
----------
2


Told you Oracle was the gift that keeps on giving.... :cool:


select idx from mytest where (case when instr(mycol,chr(13))>0 then 'Y' else 'N' end)='Y';

IDX
----------
2

[henrybluebottle]

Thanks, mixture.
So could I say "select * from myfile where mycol like '%chr(13)%' " given that I don't know where the CRs are, or what would the best syntax be ?

[mixture]

Indeed, a fourth option would be :



SQL> select idx from mytest where mycol like '%'||chr(13)||'%';

IDX
----------
2



Note the concatenation of chr, otherwise you're just searching for the string "chr(13)" rather than the character.

Apart from my first option above, either of the remaining three should work irrespective of where the character is.

what would the best syntax be ?

It depends if this is a one-off requirement because you need to "fix" some imported data, or whether it is an ongoing feature of what you are developing.

Also, depending on what you're looking to achieve, the translate or decode functions may be more what you're after.

If it's a one off, then who cares.... run it, if it takes 10 hours, then so be it, but since you'll never have to do it again it doesn't really matter. You could also use create table as select in conjunction with a translate/decode.

If it is an ongoing feature, then I suggest you read up on how to use the Oracle TKPROF tool to get query plans so that you can see what the most efficient query grammar is for your particular data. A copy of Tom's Effective Oracle By Design might also be useful !

Don't forget about FBIs (Function Based Indexes) either if this is an ongoing requirement. This would allow you to create an index based on the most appropriate syntax and query against it. For example (please note, I am NOT saying this is the best one for you to use !!!) :


create index my_idx on my_tab (case when instr(mycol,chr(13))>0 then 'Y' else null end);


I used null here instead of the 'N' in my previous example in order to keep the index at its smallest and optimised for what you're using it for, i.e. looking for columns that match).

You would then make use of your new index in a similar fashion to my previous example, however Oracle would be using the FBI instead of scanning the table.

select idx from mytest where (case when instr(mycol,chr(13))>0 then 'Y' else null end)='Y';

[henrybluebottle]

Thanks.
It would be an ongoing query but only once every couple of months. Not a large file so timing isn't an issue, and it's a vendor's database so couldn't play with indices etc. I'll try your solutions when I'm back at work late next week.
:ok:

[henrybluebottle]

Mixture,
The 4th option (like '%'||chr(13)||'%') worked perfectly.
Thanks again.