我的表的特定列中有一個字串，它具有某種固定格式。
例如：
Score breakup: {TR_SCORE=0, SAR_SCORE=2, LO_SCORE=2, CASH_SCORE=1, TPP_SCORE=1, CREDITS_SCORE=1}
我想寫一個正則運算式來分離所有的評分值，如下所示：
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 |
任何人都可以幫助創建一個正則運算式嗎？
uj5u.com熱心網友回復：
您可以將REGEXP_REPLACE()函式與\d 模式一起使用，以提取僅包含數字的子字串，例如
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/376654.html
上一篇：如何將OracleTO_NUMBER轉換為SQLServer
下一篇：基于內連接從表中洗掉記錄