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 |
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
It seems a simple REPLACE function Oracle will work for you -
SELECT REPLACE(LOBS, '4', '2')
FROM your_table;
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
