时间戳

首页 > 解决方案 > 具有多个值的 REGEXP_INSTR

sql - 具有多个值的 REGEXP_INSTR

问题描述

例如，我有两个表 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

'''

乙

<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 NEXT 1 ROWS ONLY”只返回一行，没有它我收到错误“ORA-01427：单行子查询返回多行”（逻辑）。如何将 B 中的所有数字与 A 中的所有行进行比较并接收 3 个结果？

非常感谢

更新 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.data1 是这样的：

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

  </Header>
     <Item>
    <Subitem>
    </Subitem>
  </Item>
</FVDeliveryAdvice>

和

像那样

    ----- 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

所有表中的行数太多

标签： sqloracleregexp-substr

解决方案

您在评论中声明数据是 xml，但您提供的示例数据不是有效的 xml（“ The <number> 1 </number> is cat”不是有效的 xml）。下面的解决方案使用正则表达式从<number>无效 xml 中的标签中提取 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

推荐阅读