Regular expressions examples.

Below are some examples of Oracle Regular expressions. Very small in size, but practical in use.


--- Remove from the string all characters except digits

select REGEXP_REPLACE('ads687d6905%&^ sd word','[^][[:digit:]]','') from dual

--- Remove from the string all digits

select REGEXP_REPLACE('ads687d6905%&^ sd word','[[:digit:]]','') from dual

--- Identify number of digits in the string

select length(REGEXP_REPLACE('ads687d6905%&^ sd word','[^][[:digit:]]','')) from dual

-- Find the positiong of the substring, which starts with 2 capital letters and followed by 5 digits. For example like 'CO 80016' or 'CO,80016'

select REGEXP_INSTR('11203 St Birch str, CO, 80016-','[A-Z]{2,}[ |,]*[0-9]{5}') from dual