Oracle REGEXP_SUBSTR 到 SUBSTR + INSTR [英] Oracle REGEXP_SUBSTR to SUBSTR + INSTR

问题描述

我正在尝试从 oracle 转换以下代码:

SELECT DISTINCT REPLACE(SUBSTR (REGEXP_SUBSTR (code,'[^,]+',1,LEVEL),2,8),']','') code从双CONNECT BY REGEXP_SUBSTR (code, '[^,]+', 1, LEVEL) IS NOT NULL;

请协助将其转换为一些SUBSTR/INSTR组合或CASE函数.

谢谢.

问候，

解决方案

您的代码不起作用，因为 DUAL 表没有 code 列；但是，如果我们假设您使用的是包含该列的不同表:

CREATE TABLE table_name ( code ) AS从 DUAL 中选择 '[1],[22],[333],[4444],[55555],[666666],[7777777],[88888888],[999999999],[0000000000]'

然后可以使用递归子查询分解子句:

WITH rsqfc ( code, start_pos, end_pos ) AS (选择代码，1、INSTR( 代码, ',', 1 )从表名联合所有选择代码，end_pos + 1,INSTR( 代码, ',', end_pos + 1 )从 rsqfcWHERE end_pos >0)选择不同代替(案例 end_pos当 0然后 SUBSTR( 代码, start_pos + 1, 8 )ELSE SUBSTR( 代码, start_pos + 1, LEAST( end_pos - start_pos - 1, 8 ) )结尾，']') AS 代码从 rsqfc;

输出:

<块引用><前>|代码 ||:------- ||99999999 ||4444 ||55555 ||1 ||第333话|22 ||666666 ||88888888 ||00000000 ||7777777 |

db<>fiddle 这里

I am trying to convert the following code from oracle:

SELECT DISTINCT REPLACE(SUBSTR (REGEXP_SUBSTR (code,'[^,]+',1,LEVEL),2,8),']','') code
FROM DUAL 
CONNECT BY REGEXP_SUBSTR (code, '[^,]+', 1, LEVEL) IS NOT NULL;

Please assist to convert it into some SUBSTR / INSTR combination or CASE function.

Thanks.

Regards,

解决方案

Your code does not work, as the DUAL table does not have a code column; however, if we assume that you are using a different table which does have that column:

CREATE TABLE table_name ( code ) AS
SELECT '[1],[22],[333],[4444],[55555],[666666],[7777777],[88888888],[999999999],[0000000000]' FROM DUAL

Then you can use a recursive sub-query factoring clause:

WITH rsqfc ( code, start_pos, end_pos ) AS (
  SELECT code,
         1,
         INSTR( code, ',', 1 )
  FROM   table_name
UNION ALL
  SELECT code,
         end_pos + 1,
         INSTR( code, ',', end_pos + 1 )
  FROM   rsqfc
  WHERE  end_pos > 0
)
SELECT DISTINCT
       REPLACE(
         CASE end_pos
         WHEN 0
         THEN SUBSTR( code, start_pos + 1, 8 )
         ELSE SUBSTR( code, start_pos + 1, LEAST( end_pos - start_pos - 1, 8 ) )
         END,
         ']'
       ) AS code
FROM   rsqfc;

Which outputs:

| CODE     |
| :------- |
| 99999999 |
| 4444     |
| 55555    |
| 1        |
| 333      |
| 22       |
| 666666   |
| 88888888 |
| 00000000 |
| 7777777  |

db<>fiddle here

这篇关于Oracle REGEXP_SUBSTR 到 SUBSTR + INSTR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！