SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE tbl (ID int, fld_126 VARCHAR2(100) NOT NULL); INSERT INTO tbl (ID, fld_126) WITH rs AS ( SELECT 1, 'XWID9998887766XDEVmmmXZON8XPHN9998887766' FROM dual UNION ALL SELECT 2, 'XDEVppppXWID999988877766XZON4XPHN9097976655' FROM dual ) SELECT * FROM rs; SELECT ID, j , json_value(j, '$.XWID') as XWID , json_value(j, '$.XDEV') as XDEV , json_value(j, '$.XZON') as XZON , json_value(j, '$.XPHN') as XDEV FROM tbl t CROSS APPLY ( SELECT treat(REPLACE('{' || REPLACE(REPLACE(REPLACE(REPLACE(fld_126 ,'XWID','","XWID":"') ,'XDEV','","XDEV":"') ,'XZON','","XZON":"') ,'XPHN','","XPHN":"') , '{",','{') || '"}' as json) As j FROM dual ) CrossApplied;