How to update a string by adding a character around a key value pair-CodePudding

I'm looking for an update statement to add curly brackets around each key value in the row. For example I would like to change this, [8254589:false,1956027:false,46523678:false] to this, [{8254589:false},{1956027:false},{46523678:false}]

CodePudding user response：

You can use:

UPDATE table_name
SET   value = REGEXP_REPLACE(value, '(\d :(true|false))', '{\1}')
WHERE REGEXP_LIKE( value, '[(,?\d :(true|false))*]' );

Which, for the sample data:

CREATE TABLE table_name (value VARCHAR2(100));

INSERT INTO table_name (value) VALUES ('[8254589:false,1956027:false,46523678:false]');

Changes the table to:

VALUE

[{8254589:false},{1956027:false},{46523678:false}]

VALUE
[{8254589:false},{1956027:false},{46523678:false}]

db<>fiddle here