Oracle Regular Expression - Part I

Oracle Regular Expression Examples - Part I

[mohan]

Some one asked about validating a field for numeric characters:

WITH mydata as (SELECT '123456678' as field FROM dual)  
SELECT REGEXP_SUBSTR(field, '[^[:digit:]]+') "FIELD",
        DECODE(REGEXP_SUBSTR(field, '[^[:digit:]]+'), null, 'T', 'F') "VALID"
 FROM mydata WHERE REGEXP_LIKE(field, '[[:digit:]]+');

We try to find any non-numeric char in the FIELD and use DECODE to generate True/False. See how the REGEXP_LIKE used in the WHERE and DECODE.

[nasimchaklader]

Hi Mohan
i understand what you are saying but i am having problem to understand that is here. if i run bellow query i got the same result so when and what scenario REGEXP_LIKE more efficient.

select count(*) from employees
where phone_number not like '%011.%';
--71

select count(*) from employees
where not regexp_like (phone_number, '[[:digit:]]{3}.[[:digit:]]{2}.[[:digit:]]{4}');
--71