oracle-databaseregexp-substr

Capture the 4th words using regexp_substr

How can I capture the word after 3rd gaps using regexp?

SELECT REGEXP_SUBSTR(DATA,’\s{3}[A-Z{3}\d+\.\d+’) FROM TBL

Data:

Y091234ABC     100        DEF100.25      DEF200 DEF300      DEF10000

Y091234ABC       5000.75      DEF25000 DEF200000          DEF3000    DEF10000000 DEF100000

Result:

DEF200
DEF200000
Solution

  • You define a word as a string of non-space. This is how to get the 4th one:

    SELECT REGEXP_SUBSTR(data, '[^[:space:]]+', 1, 4) FROM tbl;

    Demo: https://dbfiddle.uk/uk6EF8Fk

    Docs: https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/REGEXP_SUBSTR.html#GUID-2903904D-455F-4839-A8B2-1731EF4BD099