拆分oracle查询

57hvy0tb 于 2021-07-24 发布在 Java

关注(0)|答案(3)|浏览(335)

我有table\u x表的code\u x字段，其内容采用以下格式：

CODE02+CODE03+CODE01+......+CODE0n

我的目标是，在pl/sql过程中，使用“+”分隔符将内容分成几行，以增强临时表的code\t字段

CODE02
CODE03
CODE01
......
CODE0n

请注意，数据的顺序不得更改。
拆分字段后，我必须与每个值连接例如，用另一个表（表y）截获

select NAME_Y from TABLE_Y A
inner join TABLE_T B
on A.CODE_Y=B.CODE_T

获得类似于以下结果：

NAMECODE02
NAMECODE03
NAMECODE01
......
NAMECODE0n

其中，上述名称的顺序与先前拆分的代码相同。
提取名称后，我必须以与代码相同的方式重新压缩它们，以获得单个字段，如：

NAMECODE02+NAMECODE03+NAMECODE01+......+NAMECODE0n

我希望我是清楚的，我提前感谢你给我的帮助

sql split oracle

来源：https://stackoverflow.com/questions/63034477/split-oracle-query

3条答案

按热度按时间

vngu2lb81#

在显示时拆分内容，无论其中可能有多少个值

SQL> select regexp_substr( 'code01+code02+code03+code4' , '[^+]+' , 1, level ) from 
      dual
      connect by regexp_substr( 'code01+code02+code03+code04' , '[^+]+' , 1, level )
      is not null
      /

REGEXP_SUBSTR('CODE01+CODE02+CODE03+CODE4','[^+]+',1,LEVEL)
--------------------------------------------------------------------------------
code01
code02
code03
code04

从行返回到列

SQL>  with t as ( select 'code1' as col from dual
  2   union all
 select 'code2' as col from dual
  3    4   union all
 select 'code3' as col from dual
  5    6   union all
  7   select 'code4' as col from dual
 ) select * from t pivot ( max(col) for col in ( 'code1' as code1, 'code2' as code2, 'code3' as code3, 'code4' as code4 ) )
 /
  8    9
CODE1 CODE2 CODE3 CODE4
----- ----- ----- -----
code1 code2 code3 code4

SQL>
SQL>

如果您想要一条具有相同分隔符的记录

SQL> ed
Wrote file afiedt.buf

  1   with t as ( select 'code1' as col from dual
  2   union all
  3   select 'code2' as col from dual
  4   union all
  5   select 'code3' as col from dual
  6   union all
  7   select 'code4' as col from dual
  8   )
  9  select code1 || '+' || code2 || '+' || code3 || '+' || code4 as result
 10  from (
 11  select * from t pivot ( max(col) for col in ( 'code1' as code1, 'code2' as code2, 'code3' as code3, 'code4' as code4 ) )
 12* )
SQL> /

RESULT
-----------------------
code1+code2+code3+code4

SQL>

当然，您必须在plsql中包含这一点，以动态计算您可能有多少列（当然，限制是1000，超过这个限制是不可能的）

赞(0）回复(0）举报 2021-07-24

sz81bmfz2#

你可以用 REGEXP_SUBSTR() 与…一起工作 level 伪柱：

SELECT level AS id,
       REGEXP_SUBSTR( code_x, '[^\+]+', 1 , level ) AS code_x        
  FROM table_x t
CONNECT BY LEVEL <=  REGEXP_COUNT( code_x, '\+') + 1

以取消提取值
演示

赞(0）回复(0）举报 2021-07-24

aor9mmx13#

with t1 as
      ( SELECT 'CODE5+CODE2+CODE8+CODE4+CODE1+CODE6' txt FROM dual
       )
    SELECT 'NAME'|| regexp_substr(txt, '[^+]+', 1, LEVEL) txt
 FROM t1
 CONNECT BY regexp_substr(txt, '[^+]+', 1, LEVEL) IS NOT NULL
   SQL> /

TXT
------------------------------------------------------------------------------------------------------------------------------------------------
NAMECODE5
NAMECODE2
NAMECODE8
NAMECODE4
NAMECODE1
NAMECODE6

将上面的sql与listag组合以重新压缩

with t1 as
          ( SELECT 'CODE5+CODE2+CODE8+CODE4+CODE1+CODE6' txt FROM dual
           ),
        --replace NAME string with column NAME_Y from table_y
       t2 as (SELECT 'NAME'||regexp_substr(txt, '[^+]+', 1, LEVEL) txt
         FROM t1
         CONNECT BY regexp_substr(txt, '[^+]+', 1, LEVEL) IS NOT NULL)

    select listagg(txt,'+') txt from t2
    SQL> /

    TXT
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    NAMECODE5+NAMECODE2+NAMECODE8+NAMECODE4+NAMECODE1+NAMECODE6

赞(0）回复(0）举报 2021-07-24

我来回答

拆分oracle查询

3条答案

相关问题

热门标签

最新问答