CODEWITHSUNDEEP
sqloracle-database
Runtime Terror
Runtime Terror

Reputation: 25

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

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}]

Upvotes: 0

Views: 68

Answers (1)

MT0
MT0

Reputation: 167877

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}]

db<>fiddle here

Upvotes: 1

Related Questions