Indeed, a fourth option would be :
Code:
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 !!!) :
Code:
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.
Code:
select idx from mytest where (case when instr(mycol,chr(13))>0 then 'Y' else null end)='Y';