REGEXP_INSTR具有多个值

Question

我有两个表A和B，例如

The <number> 1 </number> is cat
The <number> 2 </number> is dog
The <number> 3 </number> is horse
The <number> 4 </number> is chicken
The <number> 5 </number> is hippo 

"

B

<id>2</id>
<id>4</id>
<id>1</id>

我想交叉检查B中的值(仅限数字)与表A中的所有值，并得到结果:

is dog
is chicken
is cat

我在"where"查询的部分像这样(只是一个例子):

where (REGEXP_INSTR ((DBMS_LOB.SUBSTR(regexp_substr(A, '<number>(.**?)(s)'))) , (DBMS_LOB.SUBSTR((select regexp_substr(B, '<id>(.*?)</id>.*',1,1,null,1) from B 
FETCH NEXT 1 ROWS ONLY ))))>0;

我的问题是"FETCH下1行只"只返回一行，如果没有，我收到错误"ORA-01427:单行子查询返回多行";(逻辑)。我如何将B中的所有数字与A中的所有行进行比较并获得3个结果?

Thank you very much

更新19/10以下是更准确的信息。

select col1
from
(select regexp_substr(B.E, '<InternalId>(.**?)(s)') as col1, (select regexp_substr(table_D.data1, '<InternalId>(.*?)</InternalId>.*',1,1,null,1) from table_F D) as col2
from table_C B 
where table_B.E like 'xxxxx')
where REGEXP_INSTR ((DBMS_LOB.SUBSTR(col1)) , (DBMS_LOB.SUBSTR(col2)))>0;

table_D。数据如下:

<?xml version="1.0"?>
<FVDeliveryAdvice>
<Header>
<InternalId>2</InternalId>
<Datatype>200</Datatype>
</Header>
<Item>
<Subitem>
</Subitem>
</Item>
</FVDeliveryAdvice>

和

B。E like that

----- PL/SQL Error Message -----
<InternalId>2</InternalId>ORA-20104: ALR not found: '0007000-00801000001'
<Status>
<Header
<InternalId>2</InternalId>
<Datatype>200</Datatype>
</Header>
<StatusInfo>
</StatusInfo>
</Status>
----- PL/SQL Error Stack -----
ORA-20104: ALR not found:'0007000-00801000001'
----- PL/SQL Call Stack -----
object      line  object
handle    number  name
00007FF9AF947390         1  anonymous block

result
ORA-01427: single-row subquery returns more than one row
Result I want:
<InternalId>2</InternalId>ORA-20104: ALR not found: '0007000-00801000001'
but for all rows

所有表的行都是太多的

Answer 1

您在注释中声明数据是xml，但是您提供的示例数据不是有效的xml ("The <number> 1 </number> is cat";是无效的(xml)。下面的解决方案使用regexp从无效xml中的<number>标记提取id，并使用XMLTYPE().EXTRACT().GETSTRINGVAL()函数从有效xml中提取id。如果您的数据是xml，请查看xml原生函数来提取数据，它们比REGEXP函数性能高得多。

WITH table_a (c) AS
(
SELECT 'The <number> 1 </number> is cat' FROM DUAL UNION ALL
SELECT 'The <number> 2 </number> is dog' FROM DUAL UNION ALL
SELECT 'The <number> 3 </number> is horse' FROM DUAL UNION ALL
SELECT 'The <number> 4 </number> is chicken' FROM DUAL UNION ALL
SELECT 'The <number> 5 </number> is hippo ' FROM DUAL 
)
, table_b (c) AS 
(
SELECT '<id>2</id>' FROM DUAL UNION ALL
SELECT '<id>4</id>' FROM DUAL UNION ALL
SELECT '<id>1</id>' FROM DUAL 
)
SELECT 
TRIM(REGEXP_REPLACE(a.c,'([^<>]+)(<number>)([^<>]+)(</number>)([^<>]+)','5')) as result
FROM table_a a 
JOIN table_b b ON TRIM(XMLTYPE(b.c).EXTRACT('/id/text()').GETSTRINGVAL()) = TRIM(REGEXP_REPLACE(a.c,'([^<>]+)(<number>)([^<>]+)(</number>)([^<>]+)','3'));
RESULT
is cat
is dog
is chicken