Oracle SQL列出Clob/Blob字段中出现的所有字符串

Question

我的Oracle表"Table1"中有一个字段，列为"texta"，其中写入了我的所有代码。我想提取所有匹配模式"record."或"record."的出现，并列出结果。例如：-

文本内容a

CreateRecord = CreateRecord(Record.PS_JOB)
CreateRecord = CreateRecord(Record.PS_NATIONAL_ID)

预期结果为

PS_JOB
PS_NATIONAL_ID

Answer 1

看看这是否有帮助。

SQL> desc test
Name                                                  Null?    Type
----------------------------------------------------- -------- -------------------
ID                                                             NUMBER
TEXTA                                                          CLOB
SQL> select * From test;
ID TEXTA
---------- ------------------------------------------------------------------------
1 CreateRecord = CreateRecord(Record.PS_JOB)
CreateRecord = CreateRecord(Record.PS
2 CreateRecord = CreateRecord(Record.ABC_DEF)
3 CreateRecord = CreateRecord(Record.LITTLE_FOOT)
SQL> select id,
2    regexp_substr(texta, 'Record.w+', 1, column_value) result
3  from test cross join
4       table(cast(multiset(select level from dual
5                           connect by level <= regexp_count(texta, 'Record.')
6                          ) as sys.odcinumberlist));
ID RESULT
---------- ------------------------------------------------------------------------
1 Record.PS_JOB
1 Record.PS_NATIONAL_ID
2 Record.ABC_DEF
3 Record.LITTLE_FOOT
SQL>