如何在oracle中从逗号分隔的字符串中删除特定值 [英] How to remove specific value from comma separated string in oracle

问题描述

我想使用oracle从逗号分隔的字符串中删除特定值.

I want remove specific value from comma separated sting using oracle.

样本输入-

col
1,2,3,4,5

假设我要从字符串中删除3.

Suppose i want to remove 3 from the string.

样本输出-

col
1,2,4,5

请建议我如何使用oracle查询来做到这一点.

Please suggest how i can do this using oracle query.

谢谢.

推荐答案

这里是一个仅使用标准字符串函数(而不是正则表达式)的解决方案-在大多数情况下，这将导致执行速度更快；仅当它是第一个字符后跟逗号，最后一个字符后有逗号，或在逗号之前和之后时才删除3，并且在中间情况下它会删除在它前面的逗号，并在中间字符中删除它后面的逗号.第一种和第三种情况.

Here is a solution that uses only standard string functions (rather than regular expressions) - which should result in faster execution in most cases; it removes 3 only when it is the first character followed by comma, the last character preceded by comma, or preceded and followed by comma, and it removes the comma that precedes it in the middle case and it removes the comma that follows it in the first and third case.

它能够连续删除两个3(其他一些提供的解决方案无法做到)，同时保留连续的逗号(大概代表NULL)，并且不会打扰像38或123这样的数字

It is able to remove two 3's in a row (which some of the other solutions offered are not able to do) while leaving in place consecutive commas (which presumably stand in for NULL) and do not disturb numbers like 38 or 123.

该策略是首先将每个逗号加倍(用,,替换,)，然后追加并在逗号前(在字符串的开头和结尾).然后删除所有出现的,3,.从左边开始，用单个,替换每个,,，最后删除前导和尾随的,.

The strategy is to first double up every comma (replace , with ,,) and append and prepend a comma (to the beginning and the end of the string). Then remove every occurrence of ,3,. From what is left, replace every ,, back with a single , and finally remove the leading and trailing ,.

with
     test_data ( str ) as (
       select '1,2,3,4,5'     from dual union all
       select '1,2,3,3,4,4,5' from dual union all
       select '12,34,5'       from dual union all
       select '1,,,3,3,3,4'   from dual
     )
select str,
       trim(both ',' from 
             replace( replace(',' || replace(str, ',', ',,') || ',', ',3,'), ',,', ',')
           ) as new_str
from   test_data
;

STR           NEW_STR
------------- ----------
1,2,3,4,5     1,2,4,5
1,2,3,3,4,4,5 1,2,4,4,5
12,34,5       12,34,5
1,,,3,3,3,4   1,,,4

4 rows selected.

注意，正如MT0所指出的(请参阅下面的注释)，如果原始字符串以逗号开头或结尾，则它将被修剪得太多.为了解决这种情况，我没有将所有内容包装在trim(both ',' from ...)中，而是将其余部分包装在了子查询中，并在外部查询中使用了类似substr(new_str, 2, length(new_str) - 2)的内容.

Note As pointed out by MT0 (see Comments below), this will trim too much if the original string begins or ends with commas. To cover that case, instead of wrapping everything within trim(both ',' from ...) I should wrap the rest within a subquery, and use something like substr(new_str, 2, length(new_str) - 2) in the outer query.

这篇关于如何在oracle中从逗号分隔的字符串中删除特定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！