How to replace part of a string in a MariaDB Server Table Column

I would like to replace part of the txt content from a MariaDB 10.5 database table with a different value.

Example:

Database: users
Table: humans
Column: area
values: as below

row 1: ["area1","area2","area 3","area 4","area6"]
row 2: ["area1"]
row 3: ["area1","area 5"]

I would like to replace area1 too area 01 from all entries

I was thinking something along the line of:

UPDATE humans
SET area = replace(area, 'area1', 'area 01')
WHERE area like '%area1%'

Any help is appreciated.

Thank You

1 Answer

You can do it using REGEXP_REPLACE as follows

UPDATE humans
SET area = REGEXP_REPLACE(area, '"area[ ]*([0-9]+)"', '"area 0\\1"')