Oracle SQL REGEXP_SUBSTR – return values from string

I’m trying to extract some data from a string in Oracle. I think I need to use REGEXP_SUBSTR, but I’m struggling with this.

Example string:

Retirement , Cost , Book : BUFFALO CA GAAP , Asset Period : Jul-23 , Asset Number : 26294 , Transaction Number : 285454

From this example string I am trying to extract the string following Book :, Asset Period :, and Asset Number :.

I’d like to return the following strings in separate columns.

BUFFALO CA GAAP
26294
285454

Can someone help with this?

REGEXP_SUBSTR(xal.DESCRIPTION,'[^_]+',1,1) 

>Solution :

You can use the query:

SELECT REGEXP_SUBSTR(value, ',\s*Book\s*:\s*(.*?)\s*,', 1, 1, NULL, 1)
         AS book,
       REGEXP_SUBSTR(value, ',\s*Asset Period\s*:\s*(.*?)\s*,', 1, 1, NULL, 1)
         AS asset_period,
       REGEXP_SUBSTR(value, ',\s*Asset Number\s*:\s*(.*?)\s*,', 1, 1, NULL, 1)
         AS asset_number,
       REGEXP_SUBSTR(value, ',\s*Transaction Number\s*:\s*(.*)', 1, 1, NULL, 1)
         AS transaction_number
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT 'Retirement , Cost , Book : BUFFALO CA GAAP , Asset Period : Jul-23 , Asset Number : 26294 , Transaction Number : 285454' FROM DUAL;

Outputs:

fiddle