使用 SQL . 提取数字逗号，与字符串 'HEADER|N1000|E1001|N1002|E1003|N1004|N

Question

'HEADER|N1000|E1001|N1002|E1003|N1004|N1005'    
'HEADER|N156|E1|N7|E122|N4|E5'   
'HEADER|E0|E1|E2|E3|E4|E5'   
'HEADER|N0|N1|N2|N3|N4|N5'
'HEADER|N125' 

如何从此字符串中提取逗号分隔格式的数字？

预期成果：

1000,1001,1002,1003,1004,1005

如何提取以 N 或 E 作为后缀/前缀的数字，即。

N1000 

预期成果：

1000,1002,1004,1005

下面的正则表达式不会返回所需的结果。但我想要这样的东西

select REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005', '.*?(d+)', '1,'), ',?.*$', '') from dual

这里的问题是 当我想要带有 E 或 N 的数字时

select REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005', '.*?N(d+)', '1,'), ',?.*$', '') from dual

select REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005', '.*?E(d+)', '1,'), ',?.*$', '') from dual

他们为这个场景提供了很好的结果

但是当我输入"标题|N1000|E1001' 它给出了错误的答案，请验证并更正

Answer 1

更新

根据对问题的更改，原始答案无效。相反，该解决方案要复杂得多，使用分层查询从字符串中提取所有数字，然后LISTAGG将从每个字符串中提取的数字列表重新组合在一起。要提取所有数字，我们使用此查询：

WITH cte AS (
SELECT DISTINCT data, level AS l, REGEXP_SUBSTR(data, '[NE]d+', 1, level) AS num FROM test
CONNECT BY REGEXP_SUBSTR(data, '[NE]d+', 1, level) IS NOT NULL
)
SELECT data, LISTAGG(SUBSTR(num, 2), ',') WITHIN GROUP (ORDER BY l) AS "All numbers"
FROM cte
GROUP BY data

输出(对于新的示例数据(：

DATA                                        All numbers
HEADER|E0|E1|E2|E3|E4|E5                    0,1,2,3,4,5
HEADER|N0|N1|N2|N3|N4|N5                    0,1,2,3,4,5
HEADER|N1000|E1001|N1002|E1003|N1004|N1005  1000,1001,1002,1003,1004,1005
HEADER|N125                                 125
HEADER|N156|E1|N7|E122|N4|E5                156,1,7,122,4,5

为了只选择以E开头的数字，我们修改查询以将REGEXP_SUBSTR表达式中的[EN]替换为仅E即

SELECT DISTINCT data, level AS l, REGEXP_SUBSTR(data, 'Ed+', 1, level) AS num FROM test
CONNECT BY REGEXP_SUBSTR(data, 'Ed+', 1, level) IS NOT NULL

输出：

DATA                                        E-numbers
HEADER|E0|E1|E2|E3|E4|E5                    0,1,2,3,4,5
HEADER|N0|N1|N2|N3|N4|N5    
HEADER|N1000|E1001|N1002|E1003|N1004|N1005  1001,1003
HEADER|N125     
HEADER|N156|E1|N7|E122|N4|E5                1,122,5

可以进行类似的更改以提取以N开头的数字。

在 dbfiddle 上演示

原始答案

实现所需结果的一种方法是用该数字和逗号替换导致数字的字符串，然后替换结果中从字符串末尾,|到末尾的任何字符：

SELECT REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005|', '.*?(d+)', '1,'), ',?|.*$', '') FROM dual

输出：

1000,1001,1002,1003,1004,1005

要仅输出以N开头的数字，我们将其添加到捕获组之前的前缀字符串中：

SELECT REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005|', '.*?N(d+)', '1,'), ',?|.*$', '') FROM dual

输出：

1000,1002,1004,1005

要仅输出以E开头的数字，我们将其添加到捕获组之前的前缀字符串中：

SELECT REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005|', '.*?E(d+)', '1,'), ',?|.*$', '') FROM dual

输出：

1001,1003

在 dbfiddle 上演示

Answer 2

我不知道你正在使用什么DBMS，但这里有一种方法可以在Postgres中做到这一点：

WITH cte AS (
SELECT CAST('HEADER|N1000|E1001|N1002|E1003|N1004|N1005|' AS VARCHAR(1000)) AS myValue
)
SELECT SUBSTRING(MyVal FROM 2)
FROM (
SELECT REGEXP_SPLIT_TO_TABLE(myValue,'|') MyVal
FROM cte
) src
WHERE SUBSTRING(MyVal FROM 1 FOR 1) = 'N'
;

SQL 小提琴

Answer 3

据我了解的问题，您想从字符串中提取以 N 开头的子字符串，您可以尝试以下(然后如果需要，您可以合并用逗号分隔的输出(

select REPLACE(value, 'N', '') from STRING_SPLIT('HEADER|N1000|E1001|N1002|E1003|N1004|N1005|', '|') where value like 'N%'

输出：

1000 1002 1004 1005

年