How to replace a value in a comma separated string column in Oracle-CodePudding

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

CodePudding user response：

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

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

CodePudding user response：

In Oracle,

SELECT id,
       TRIM(BOTH ',' FROM REPLACE(','||lobs||',', ',4,', ',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;

Outputs:

ID UPDATED_LOBS

1 1,2,64,7,8

2 2,1,5,64,7,9

3 3,5,64,8,11,2

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

CodePudding user response：

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