Oracle SQL: Regex for finding out values from a Stringified map of fixed format-CodePudding

I have a String in a specific column of my table which is of a certain fixed format.

For ex:

Score breakup: {TR_SCORE=0, SAR_SCORE=2, LO_SCORE=2, CASH_SCORE=1, TPP_SCORE=1, CREDITS_SCORE=1}

I wish to write a regex to separate out all the scoring values as below:

SELECT TR_SCORE, SAR_SCORE, LO_SCORE 
  FROM some_table 
 WHERE message LIKE 'Score breakup:%';

TR_SCORE	SAR_SCORE	LO_SCORE
0	2	3
1	3	0

Can anyone help create a regex for the same?

CodePudding user response：

You can use REGEXP_REPLACE() function along with \d pattern in order to extract the substrings with digits only such as

SELECT col,
       REGEXP_REPLACE(col,'(.*TR_SCORE=)(\d )(.*)','\2') AS tr_score,
       REGEXP_REPLACE(col,'(.*SAR_SCORE=)(\d )(.*)','\2') AS sar_score,
       REGEXP_REPLACE(col,'(.*LO_SCORE=)(\d )(.*)','\2') AS lo_score
  FROM t

Demo