How to extract everything after 1st series of numbers PL/SQL

157 views Asked by At

I want to extract everything after the 1st series of numbers. For example, the outcome of 95a6 should be 95 and a6. And the outcome of 9B2 should be 9 and B2

1

There are 1 answers

5
Salman A On BEST ANSWER

You can use REGEXP_SUBSTR for this:

SELECT str
     , REGEXP_SUBSTR(str, '\d+') AS substr1
     , REGEXP_SUBSTR(str, '[A-Za-z].*') AS substr2
FROM (
    SELECT '95a6' AS str FROM DUAL UNION
    SELECT '9 B2' FROM DUAL
) tests

Here \d+ matches a sequence of digits and [A-Za-z].* matches a letter and everything after it.

Demo on db<>fiddle