We have 2 tables as shown below:

Table A:

Table B:

Please help me with an Oracle query which replaces part of the description column present in Table A from above table. The numbers present after text "to:" i.e., 1111 and 5555 of Table A (description column)should be compared with COL1 of Table B and replace with corresponding COL2 value.

For example : replace 1111 with 222 in Table A replace 5555 with 666 in Table A

Table A should look like this post running of the query.

Table A:

Thanks in advance :)

CodePudding user response：

You can use techniques dedicated to JSON within a PL/SQL code values such as

DECLARE
    v_jsoncol       tableA.description%TYPE;
    v_json_obj      json_object_t;
    v_new_jsoncol   tableA.description%TYPE;
    v_col1          tableB.col1%TYPE;
    v_col2          VARCHAR2(25);
    l_key_list      json_key_list;     
BEGIN
    FOR c IN
      (
        SELECT *
          FROM tableA
      )
    LOOP
      v_json_obj := TREAT(json_element_t.parse(c.description) AS json_object_t);
      l_key_list := v_json_obj.get_keys; 
 
      FOR i IN 1 .. l_key_list.COUNT 
      LOOP 
        IF l_key_list (i) = 'to' THEN
           v_col1 := v_json_obj.get_string (l_key_list (i));
          SELECT TO_CHAR(col2)
            INTO v_col2
            FROM tableB
           WHERE col1 = v_col1;
           v_json_obj.put(l_key_list (i),v_col2); 
           v_new_jsoncol := v_json_obj.to_string; 

           UPDATE tableA SET description = v_new_jsoncol WHERE row_num = c.row_num;
        END IF;
      END LOOP;      
   END LOOP;
END;
/

Demo

CodePudding user response：

I used instr to get 3rd and 4th " chars position to get the value inside and replace it with other query .

Note : ROWNUM , description is reserved keywords, so i advise not to use them as column names

here is the final code:

SELECT  ROWNUM , 
REPLACE (description , 
SUBSTR(  description , INSTR(description, '"', 1, 3) 1,
          INSTR(description, '"', 1, 4) -  INSTR(description, '"', 1, 3)-1) ,
(select COL2 from tblB where COL1 =  
SUBSTR(  description , INSTR(description, '"', 1, 3) 1,
          INSTR(description, '"', 1, 4) -  INSTR(description, '"', 1, 3)-1)
) 
  )        
 from tblA 

CodePudding user response：

Don't use string functions for this. You should use JSON functions and can use JSON_MERGEPATCH:

MERGE INTO table_a dst
USING (
  SELECT a.ROWID AS rid,
         b.col2
  FROM   table_a a
         INNER JOIN table_b b
         ON JSON_VALUE(a.description, '$.to' RETURNING VARCHAR2(10)) =  b.col1
) src
ON (dst.ROWID = src.RID)
WHEN MATCHED THEN
  UPDATE
  SET description = JSON_MERGEPATCH(
                      dst.description,
                      JSON_OBJECT(KEY 'to' VALUE src.col2)
                    );

Which, for your sample data:

CREATE TABLE Table_A (description CLOB CHECK (description IS JSON));

INSERT INTO table_a (description)
SELECT '{"to": " 1111", "from": "9999"}' FROM DUAL UNION ALL
SELECT '{"to": " 5555", "from": "8888"}' FROM DUAL;

CREATE TABLE Table_B (COL1, COL2) AS
SELECT ' 1111', 222 FROM DUAL UNION ALL
SELECT ' 5555', 666 FROM DUAL;

Then:

SELECT * FROM table_a;

Outputs:

DESCRIPTION
{"to":222,"from":"9999"}
{"to":666,"from":"8888"}

db<>fiddle here