Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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;
1 rows affected

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;


BOOK
ASSET_PERIOD
ASSET_NUMBER
TRANSACTION_NUMBER


BUFFALO CA GAAP
Jul-23
26294
285454