How to remove specific value from comma separated string in oracle

Question

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

Sample Input -

col
1,2,3,4,5

Suppose i want to remove 3 from the string.

Sample Output -

col
1,2,4,5

Please suggest how i can do this using oracle query.

Thanks.

Answer 1

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.

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.

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.

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.

Answer 2

Here is one method:

select trim(both ',' from replace(',' || '1,2,3,4,5' || ',', ',' || '3' || ',', ','))

That said, storing comma-delimited strings is a really, really bad idea. There is almost no reason to do such a thing. Oracle supports JSON, XML, and nested tables -- all of which are better alternatives.

The need to remove an element suggests a poor data design.

Answer 3

You can convert the list rows using an XMLTABLE, filter to remove the unwanted rows and then re-aggregate them:

SELECT LISTAGG( x.value.getStringVal(), ',' ) WITHIN GROUP ( ORDER BY idx )
FROM   XMLTABLE(
         ( '1,2,3,4,5' )
         COLUMNS value XMLTYPE PATH '.',
                 idx   FOR ORDINALITY
       ) x
WHERE  x.value.getStringVal() != 3;

For a simple filter this is probably not worth it and you should use something like (based on @mathguy's solution):

SELECT SUBSTR( new_list, 2, LENGTH( new_list ) - 2 ) AS new_list
FROM   (
  SELECT REPLACE(
           REPLACE(
             ',' || REPLACE( :list, ',', ',,' ) || ',',
             ',' || :value_to_replace || ','
           ),
           ',,',
           ','
         ) AS new_list
  FROM   DUAL
)

However, if the filtering is more complicated then it might be worth converting the list to rows, filtering and re-aggregating.

Answer 4

I do not knwo how to do this in Oracle, but with SQL-Server I'd use a trick:

• convert the list to XML by replacing the comma with tags
• use XQuery to filter the data
• reconcatenate

This is SQL Server syntax but might point you the direction:

declare @s varchar(100)='1,2,2,3,3,4';
declare @exclude int=3;

WITH Casted AS
(
    SELECT CAST('<x>' + REPLACE(@s,',','</x><x>') + '</x>' AS XML) AS TheXml
)
SELECT x.value('.','int')
FROM Casted
CROSS APPLY TheXml.nodes('/x[text()!=sql:variable("@exclude")]') AS A(x)

UPDATE

I just found this answer which seems to show pretty well how to start...

Answer 5

I agree with Gordon regarding the fact that storing comma delimited data in a column is a really bad idea.

I just preceed the csv with a ',', then use the replace function followed by a left trim function to clean-up the preceeding ','.

SCOTT@tst>VAR b_number varchar2(5);
SCOTT@tst>EXEC :b_number:= '3';

PL/SQL procedure successfully completed.

SCOTT@tst>WITH srce AS (
  2      SELECT
  3          ',' || '3,1,2,3,3,4,5,3' col
  4      FROM
  5          dual
  6  ) SELECT
  7      ltrim(replace(col,',' ||:b_number),',') col
  8  FROM
  9      srce;
COL      
1,2,4,5