Matching beginning of line in oracle regexp_like

Question

I am confused about support for ^ as beginning of line anchor in oracle.

Below queries returns nothing:

select 1 from dual where regexp_like('1000C', '^[\dA-Z]+$');
select 1 from dual where regexp_like('1000C', '^1[\dA-Z]+$');

Same queries without ^ in the beginning seems to work fine:

select 1 from dual where regexp_like('1000C', '[\dA-Z]+$');

To my surprise below query also did not work:

select 1 from dual where regexp_like('1000C', '^1[\dA-Z]+');

However, without [\dA-Z]+ , ^seems to work:

select 1 from dual where regexp_like('1000C', '^1');

I tested 1000C against expression ^[\dA-Z]+$ in https://regex101.com/, it matches correctly in all the flavours on the site.

You focused on the wrong pattern. Shorthand character classes cannot be used in bracket expressions in Oracle regex, that is all there is to it. — Wiktor Stribiżew, Apr 30 '19 at 07:55
Thanks Wiktor, can you please provide which question makes it duplicate? — Pavan Kumar, Apr 30 '19 at 10:28

score 2 · Accepted Answer · answered Apr 30 '19 at 07:28

2

I was unable to get either \d or \\d inside a character class to work correctly here. But, [0-9] seems to work:

select 1 from dual where regexp_like('1000C', '^1[0-9A-Z]+');

The reason your second query works:

select 1 from dual where regexp_like('1000C', '[\dA-Z]+$');

is that it is matching the last C in the input string. But, none of the preceding numbers are actually being matched.

answered Apr 30 '19 at 07:28

Tim Biegeleisen

@PushpeshKumarRajwanshi Most of the time, for regex questions, you really need to test using the exact tool or language. A generic regex answer might not work on a given SQL database. – Tim Biegeleisen Apr 30 '19 at 07:42

1 Answers1