我的Oracle表“ Table1”中有一个字段，列“ texta”记录了我所有的代码。 我想提取所有与模式“记录”匹配的事件。或“记录”。并列出结果。例如：-
文本内容
CreateRecord = CreateRecord(Record.PS_JOB)
CreateRecord = CreateRecord(Record.PS_NATIONAL_ID)
预期结果是
PS_JOB
PS_NATIONAL_ID
Oracle SQL在Clob / Blob字段中列出所有出现的字符串
•
问答
liukai1224 回答：Oracle SQL在Clob / Blob字段中列出所有出现的字符串
看看是否有帮助。
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>