'How to replace a value in a comma separated string column in Oracle

I want to replace all the occurrences of 4 with the number 2 in a string column of a table. This is a hardcoded value of 2 which replaces all occurrences of the number 4 in a Oracle table. The LOBS column is a VARCHAR column

ID LOBS
1 1,4,6,7,8
2 1,5,6,7,9,4
3 3,5,7,8,11,4

New Table

ID LOBS
1 1,2,6,7,8
2 1,5,6,7,9,2
3 3,5,7,8,11,2
sqloracle


Solution 1:[1]

In Oracle, you can use simple (quick) string functions:

SELECT id,
       TRIM(BOTH ',' FROM REPLACE(','||lobs||',', ',4,', ',2,'))
         AS updated_lobs
FROM   table_name;

Or (slower) regular expressions:

SELECT id,
       REGEXP_REPLACE(lobs,'(^|,)4(,|$)','\12\2') AS updated_lobs
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (ID, LOBS) AS
SELECT 1, '1,4,64,7,8' FROM DUAL UNION ALL
SELECT 2, '4,1,5,64,7,9' FROM DUAL UNION ALL
SELECT 3, '3,5,64,8,11,4' FROM DUAL;

Both output:

ID UPDATED_LOBS
1 1,2,64,7,8
2 2,1,5,64,7,9
3 3,5,64,8,11,2

db<>fiddle here

Solution 2:[2]

It seems a simple REPLACE function Oracle will work for you -

SELECT REPLACE(LOBS, '4', '2')
  FROM your_table;

Solution 3:[3]

You could use REGEP_REPLACE

CREATE TABLE table1 (
  ID INTEGER,
  LOBS VARCHAR2(12)
);

INSERT INTO table1
  (ID, LOBS)
VALUES
  ('1', '1,2,6,7,8');

INSERT INTO table1
  (ID, LOBS)
VALUES
  ('2', '2,6,7,8,9');

  INSERT INTO table1
  (ID, LOBS)
VALUES
  ('3', '1,5,6,7,9,2');

  INSERT INTO table1
  (ID, LOBS)
VALUES
  ('4', '3,5,7,8,11,2');

SELECT REGEXP_REPLACE(
       REGEXP_REPLACE(
       REGEXP_REPLACE(LOBS,'^2,','4,')
       ,'(,2,)',',4,'),',2$',',4') FROM table1
       WHERE 1= 1

| REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(LOBS,'^2,','4,'),'(,2,)',',4,'),',2$',',4') |
| :--------------------------------------------------------------------------------------- |
| 1,4,6,7,8                                                                                |
| 4,6,7,8,9                                                                                |
| 1,5,6,7,9,4                                                                              |
| 3,5,7,8,11,4                                                                             |

db<>fiddle here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Ankit Bajpai
Solution 3 nbk

Related Questions