CODEWITHSUNDEEP
sqloracle-databaseregexp-like
Zeus
Zeus

Reputation: 6566

Why doesn't this regexp_like does not get the results I want

I'm trying the following regexp_like for a bigger query, it does not work, what am i doing wrong? 

with xx as
  (select '333-22-234223' as a
   from dual)
select xx.a
from xx
where
  regexp_like(xx.a,'^[:digit:]{3}-[:digit:]{2}-[:digit:]{6}$');

Upvotes: 1

Views: 87

Answers (3)

Gary_W
Gary_W

Reputation: 10360

For the sake of completeness, you can use \d for a digit as well:

with xx as
  (select '333-22-234223' as a
   from dual)
select xx.a
from xx
where regexp_like(xx.a,'^\d{3}-\d{2}-\d{6}$');

Upvotes: 1

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use the following solution using [[:digit:]] (double [...]):

WITH xx AS (
    SELECT '333-22-234223' AS a FROM dual
)
SELECT xx.a
FROM xx
WHERE REGEXP_LIKE(xx.a, '^[[:digit:]]{3}-[[:digit:]]{2}\-[[:digit:]]{6}$');

... or using [0-9] instead of [[:digit:]]:

WITH xx AS (
    SELECT '333-22-234223' AS a FROM dual
)
SELECT xx.a
FROM xx
WHERE REGEXP_LIKE(xx.a, '^[0-9]{3}-[0-9]{2}\-[0-9]{6}$');

demo: http://sqlfiddle.com/#!4/3149e4/120/1

Why does it require double brackets?

These character classes are valid only inside the bracketed expression.
source: https://docs.oracle.com/cd/B12037_01/server.101/b10759/ap_posix001.htm

Upvotes: 3

Strikegently
Strikegently

Reputation: 2441

An alternative to @Sebastion Brosch

You can replace the character class ([:digit:]) with an explicit range of digits, like this:

with xx as
(select '333-22-234223' as a
from dual)
select xx.a
from xx
where
regexp_like(xx.a,'^[0-9]{3}-[0-9]{2}-[0-9]{6}$');

Upvotes: 2

Related Questions