Tags
One of the frequently asked questions about REGEXP in Oracle SQL are like “How to extract string before specific character?”. Let’s look at the following simple example:
Requirement : Extract everything after dot from string ‘123abc.xyz456‘ i.e. output should be ‘123abc‘.
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 123abc
How it works :
- [^.] negated character class to match anything except for a dot
- + quantifier to match one or more of these
Hope it helps!