Oracle SQL获取第n个元素正则表达式 [英] Oracle SQL getting the nth element regexp

问题描述

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

I am trying to get the nth element in a comma separated string using SQL in Oracle.

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

I have the following so far..

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

但是当元素为空(即，任何人都可以帮忙吗?)时它不起作用?

but it doesn't work when the element is empty i.e. ,, can anyone help?

推荐答案

如果定界值在逗号之间始终是字母数字，则可以尝试:

If your delimited values are always alphanumeric in between the commas then you could try:

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

获取第七个值(包括结尾的逗号).如果为空，则只需输入逗号(可以轻松删除).

To get the seventh value (including the trailing comma). If it is empty you just get the trailing comma (which you can easily remove).

很明显，如果您想要的值不是第七个，则将第四个参数值更改为想要的第n个出现率，例如

Obviously, if you want a value other than the seventh then change the fourth parameter value to whichever nth occurrance you want e.g.

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

由于我喜欢REGEX，所以这是一个解决方案，它也删除了结尾的逗号

As I love REGEX here is a solution that also removes the trailing comma

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

希望有帮助

这篇关于Oracle SQL获取第n个元素正则表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！