提问人：Almosino 提问时间：11/13/2023 最后编辑：Wiktor StribiżewAlmosino 更新时间：11/14/2023 访问量：58
在sql中两个单词之间提取带有正则表达式的文本
Extract text with regex between two words in sql
问：
我有一个名为 TABLE 的表，它有一个名为 MESSAGE 的列。此列具有以下值：
returning: -1 -> java.sql.SQLException: ORA-20000: SAMPLETEXTORA-06512
returning: -1 -> java.sql.SQLException: ORA-20000: SAMPLETEXT1ORA-O6512
returning: -1 -> java.sql.SQLException: ORA-01403: NO data availableORA-06512
returning: -1 -> java.sql.SQLException: ORA-20001: errorORA-06512
我想使用 Oracle sql 查询来解析数据并获取一个名为 Error_code 的列，其中包含错误代码和一个包含第一个 ORA 和第二个 ORA 之间消息的Error_message。
预期成果：
Error_code Error_message
ORA-20000 SAMPLETEXT
ORA-20000 SAMPLETEXT1
ORA-01403 NO data available
ORA-20001: error
我使用了以下查询：
SELECT
REGEXP_SUBSTR(message, 'ORA-[0-9]+') AS Error_Code,
REGEXP_SUBSTR(message, '(?<=ORA-[0-9]+: ).*(?<=ORA)', 1, 1, 'n') AS Error_Message
FROM TABLE
返回结果：
Error_code Error_message
ORA-20000
ORA-20000
ORA-01403
ORA-20001
答：
2赞
Littlefoot
11/13/2023
#1
如果它不必是正则表达式解决方案，则旧组合在大型数据集上的性能会更好。substr + instr
示例数据：
SQL> with test (col) as
2 (select 'returning: -1 -> java.sql.SQLException: ORA-20000: SAMPLETEXTORA-06512' from dual union all
3 select 'returning: -1 -> java.sql.SQLException: ORA-20000: SAMPLETEXT1ORA-O6512' from dual union all
4 select 'returning: -1 -> java.sql.SQLException: ORA-01403: NO data availableORA-06512' from dual union all
5 select 'returning: -1 -> java.sql.SQLException: ORA-20001: errorORA-06512' from dual
6 )
查询：
7 select col,
8 substr(col, instr(col, 'ORA', 1, 1),
9 instr(col, 'ORA', 1, 2) - instr(col, 'ORA', 1, 1)
10 ) result
11 from test;
COL RESULT
----------------------------------------------------------------------------- ------------------------------
returning: -1 -> java.sql.SQLException: ORA-20000: SAMPLETEXTORA-06512 ORA-20000: SAMPLETEXT
returning: -1 -> java.sql.SQLException: ORA-20000: SAMPLETEXT1ORA-O6512 ORA-20000: SAMPLETEXT1
returning: -1 -> java.sql.SQLException: ORA-01403: NO data availableORA-06512 ORA-01403: NO data available
returning: -1 -> java.sql.SQLException: ORA-20001: errorORA-06512 ORA-20001: error
SQL>
0赞
Barbaros Özhan
11/13/2023
#2
您可以从 [^： ] 的最后一次出现开始，然后取部分直到结尾 +$，例如
SELECT REGEXP_SUBSTR(message, 'ORA-[0-9]+') AS Error_Code,
REGEXP_SUBSTR(message, '([^: ])+$') AS Error_Message
FROM "TABLE"
前提是该列的数据模型在整个表中相同
0赞
Reilas
11/14/2023
#3
这是一个参考。
对于Error_Code，请使用匹配模式。
(?=ORA-\d+).+?(?=:)
而且，为了Error_Message，请使用捕获模式。
ORA-\d+:\s+(.+)ORA
评论
0赞
astentx
11/14/2023
Oracle 不支持环理。
评论
REGEXP_SUBSTR(message, 'ORA-[0-9]+: (.*?)(ORA|$)', 1, 1, 'n', 1)