Oracle SQL获取第n个元素的正则表达式

Question

我正在尝试使用Oracle中的SQL获取逗号分隔字符串中的第n个元素。

到目前为止，我有以下内容...

SELECT regexp_substr(
   '100016154,5101884LT00001,,,,,100000010892100000012655,L,SEI,5101884LT00001,1,SL,3595.03,00,2,N,N,G,N',
   '[^,]+',
   1,
   7)
FROM dual;

但是当元素为空时，即 ,, 它不起作用，有人可以帮忙吗？

- Jon H

---

当你在关系型数据库中看到CSV或其他序列化的值时，你就知道有些不对劲。 - NullUserException

@NullUserException，说得好。不幸的是，我也遇到过太多次了 :-( - Ollie

Answer 1

如果你的分隔值在逗号之间总是字母数字的话，你可以尝试以下方法：

SELECT REGEXP_SUBSTR( <delimied_string>, '[[:alnum:]]{0,},', 1, 7 )
  FROM dual;

获取第七个值（包括尾随逗号）。如果为空，只获取尾随逗号（可以轻松删除）。

显然，如果您想要第七个以外的值，则将第四个参数值更改为您想要的任何第n次出现。

SELECT REGEXP_SUBSTR( <delimied_string>, '[[:alnum:]]{0,},', 1, <nth occurance> )
  FROM dual;

编辑：由于我喜欢正则表达式，因此这里提供一种解决方法，可以同时删除尾部逗号。

SELECT REPLACE(
          REGEXP_SUBSTR(<delimied_string>, '[[:alnum:]]{0,},', 1, <nth>), 
          ','
       )
  FROM dual;

希望能对您有所帮助。

- Ollie

---

你好Ollie，我有同样的问题，但是你的建议在我的情况下不起作用。我需要从字符串中取第三个值 select regexp_substr('SENDER ,3B13 ,3 ,300 , , , , , ,', '[[:alnum:]]{0,},', 1, 2) from dual; 但是我只得到了 , 字符串。请问我做错了什么？ - Reddy SK

Answer 2

除非你卡在正则表达式上，否则这个也可以工作：

WITH q AS (
SELECT '100016154,5101884LT00001,,,,,100000010892100000012655,L,SEI,5101884LT00001,1,SL,3595.03,00,2,N,N,G,N' thestring FROM dual
)
SELECT SUBSTR(thestring, INSTR(thestring,',',1,6)+1, 
                         INSTR(thestring,',',1,7)-INSTR(thestring,',',1,6)-1) "The Element"
  FROM q;

The Element
------------------------
100000010892100000012655

另一种可能性是，您没有指定数据的来源。您是否可以使用外部表来读取输入源并通过SQL进行处理？

- DCookie

---

在语句中调用INSTR三次与仅调用REGEXP_SUBSTR一次相比，是否会有额外的开销？（我意识到分隔字符串的长度没有被指定，这将是一个因素）。 - Ollie

@Ollie，只有通过测量结果，你才能确定。 REGEXP_*函数也不是没有它们自己的开销问题。 - DCookie

Answer 3

你可以用一个小技巧来实现：先将所有逗号替换为逗号后跟一个空格，然后跳过那个额外的前导空格。

SQL> with data as
  2  ( select '100016154,5101884LT00001,,,,,100000010892100000012655,L,SEI,5101884LT00001,1,SL,3595.03,00,2,N,N,G,N' txt
  3      from dual
  4  )
  5  select regexp_substr(txt,'[^,]+',1,7)                             seventh_element_wrong
  6       , replace(txt,',',', ')                                      with_extra_space_after_comma
  7       , regexp_substr(replace(txt,',',', '),'[^,]+',1,7)           seventh_element_leading_space
  8       , substr(regexp_substr(replace(txt,',',', '),'[^,]+',1,7),2) the_seventh_element
  9    from data
 10  /

S WITH_EXTRA_SPACE_AFTER_COMMA
- ----------------------------------------------------------------------------------------------------------------------
SEVENTH_ELEMENT_LEADING_S THE_SEVENTH_ELEMENT
------------------------- ------------------------
1 100016154, 5101884LT00001, , , , , 100000010892100000012655, L, SEI, 5101884LT00001, 1, SL, 3595.03, 00, 2, N, N, G, N
 100000010892100000012655 100000010892100000012655

祝好，
罗布。

- Rob van Wijk

Answer 4

SELECT rtrim(regexp_substr('100016154,5101884LT00001,,,,,100000010892100000012655,L,SEI,5101884LT00001,1,SL,3595.03,00,2,N,N,G,N','[^,]{0,}[,]?',1,7),',')
FROM dual;

- stan