REGEX从列表中选择第n个值，允许为空 [英] REGEX to select nth value from a list, allowing for nulls

问题描述

我正在使用REGEXP_SUBSTR()从逗号分隔的列表中返回第n个值.当所有值都存在时，此方法工作正常，但如果项目为null，则失败.这是一个示例，其中所有值都存在，并且我选择第二次出现的不是逗号的1个或多个字符:

I am using REGEXP_SUBSTR() to return the nth value from a comma-separated list. This works fine when all values are present, but fails if an item is null. Here is an example that works where all values are present and I am selecting the 2nd occurrence of 1 or more characters that are not a comma:

SQL> select REGEXP_SUBSTR('1,2,3,4,5,6', '[^,]+', 1, 2) data
  2  from dual;

D
-
2

但是当第二个值为null时，我实际上是列表中的第三个项目，当然，实际上是第二个出现的不是逗号的1个或多个字符.但是，由于第二项为空，我需要它返回NULL:

But when the second value is null, I am really getting the third item in the list, which of course really is the 2nd occurrence of 1 or more characters that are not a comma. However, I need it to return NULL as the 2nd item is empty:

SQL> select REGEXP_SUBSTR('1,,3,4,5,6', '[^,]+', 1, 2) data
  2  from dual;

D
-
3

如果我将正则表达式更改为允许零个或多个字符(而不是1个或多个)，则对于空值之后的数字也将失败:

If I change the regex to allow for zero or more characters instead of 1 or more, it also fails for numbers past the null:

SQL> select REGEXP_SUBSTR('1,,3,4,5,6', '[^,]*', 1, 4) data
  2  from dual;

D
-
3

我需要允许使用null，但似乎无法正确使用语法.从逻辑上讲，无论是否存在数据，我都需要返回逗号第n次出现之前的值(并且还允许最后一个值).有什么想法吗?

I need to allow for the null but can't seem to get the syntax right. Logically I need to return what is before the nth occurrence of a comma whether data is present or not (and allow for the last value also). Any ideas?

推荐答案

感谢那些回答.在仔细阅读了您的答案和提供的链接中的答案之后，我得出了以下解决方案:

Thanks to those who replied. After perusing your answers and the answers in the link supplied, I arrived at this solution:

SQL> select REGEXP_SUBSTR('1,,3,4,5', '(.*?)(,|$)', 1, 2, NULL, 1) data
  2  from dual;

Data
----

这可以描述为查看第二个出现的零个或多个字符的可选集合，其后跟一个逗号或该行的末尾，然后返回第一个子组(该数据减去逗号或行的结尾).

Which can be described as "look at the 2nd occurrence of an optional set of zero or more characters that are followed by a comma or the end of the line, and return the 1st subgroup (which is the data less the comma or end of the line).

我忘了提到我在各个位置使用null进行测试，多个null，选择各种位置等等.

I forgot to mention I tested with the null in various positions, multiple nulls, selecting various positions, etc.

我唯一可以发现的警告是，如果您要查找的字段大于可用的数字，它只会返回NULL，因此您需要意识到这一点.对我来说，这不是问题.

The only caveat I could find is if the field you look for is greater than the number available, it just returns NULL so you need to be aware of that. Not a problem for my case.

为了将来可能会偶然发现此问题的搜索者的利益，我正在更新已接受的答案.

I am updating the accepted answer for the benefit of future searchers that may stumble upon this.

下一步是封装代码，以便可以将其制成一个更简单，可重用的函数.这是函数源:

The next step is to encapsulate the code so it can be made into a simpler, reusable function. Here is the function source:

  FUNCTION  GET_LIST_ELEMENT(string_in VARCHAR2, element_in NUMBER, delimiter_in VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
    BEGIN
      RETURN REGEXP_SUBSTR(string_in, '(.*?)(\'||delimiter_in||'|$)', 1, element_in, NULL, 1);
  END GET_LIST_ELEMENT;

这对可能不太适应正则表达式的开发人员隐藏了正则表达式的复杂性，并在使用时使代码更清晰.这样调用即可获得第四个元素:

This hides the regex complexities from developers who may not be so comfortable with it and makes the code cleaner anyway when in use. Call it like this to get the 4th element:

select get_list_element('123,222,,432,555', 4) from dual;

这篇关于REGEX从列表中选择第n个值，允许为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！