Search code examples
regexoracleregexp-like

Oracle - How to find carriage return, new line and tab using REGEXP_LIKE?

I am trying to run a query in Oracle 11g where I am looking in a VARCHAR column for any rows that contain any of a carriage return, new line or tab. So far my code is as shown

select c1 from table_name where regexp_like(c1, '[\r\n\t]')

Not sure why but I am getting unexpected results. I saw some mention that Oracle doesnt support '\r' or any of the other characters I used? Some folks mentioned to use chr(10) for example and then I tried the following code

select c1 from table_name where regexp_like(c1, '[chr(10)|chr(13)]')

And again I am getting unexpected results. Pretty sure I am misunderstanding something here and I was hoping for some guidance.

Solution

  • You can use:

    select c1
from   table_name
where  c1 LIKE '%' || chr(10) || '%'
or     c1 LIKE '%' || chr(13) || '%'
or     c1 LIKE '%' || chr(9) || '%';

    or

    select c1
from   table_name
where  regexp_like(c1, '[' || chr(10) || chr(13) || chr(9) || ']')

    fiddle

    where regexp_like(c1, '[\r\n\t]') does not work as you are matching any character that is \ or r or \ or n or \ or t (and not matching the perl-like character sets \r, \n or \t).

    where regexp_like(c1, '[chr(10)|chr(13)]') does not wotk as you are matching any character that is c or h or r or ( or 1 or 0 or ) or | or c or h or r or ( or 1 or 3 or ) as you have a string literal and are not evaluating the contents of the literal. If you want to evaluate them as calls to the CHR function then it must be outside the string literal as the second example above.