Tags

, ,

One of the frequently asked questions about REGEXP in Oracle SQL are like “How to extract string after specific character?”. Let’s look at the following simple example:

Requirement : Extract everything after dot from string ‘123abc.xyz456‘ i.e. output should be ‘xyz456‘.

SQL> WITH data AS
2 (
3    SELECT '123abc.xyz456' str FROM dual
4 )
5 SELECT str,
6        REGEXP_SUBSTR(str, '[^.]+$') new_str
7 FROM data;

STR           NEW_STR
------------- -------------
123abc.xyz456 xyz456

How it works :

  • [^.] negated character class to match anything except for a dot
  • + quantifier to match one or more of these
  • $ anchor to restrict matches to the end of the string

 

Hope it helps!