Oracle Replace Function

I need to replace the Table1's filed values from Table2's values while select query. Eg: Table1: Org Permission -------------------------------------- Company1

Solution 1:

If you don't want to update the existing table and only want to select the data then you can use this somewhat laborious query.

http://sqlfiddle.com/#!4/22909/4

WITH changed_table AS
     (SELECT val1, EXTRACTVALUE (x.COLUMN_VALUE, 'e') val2new
        FROM (SELECT val1, val2 xml_str
                FROM table1),
             TABLE (XMLSEQUENCE (XMLTYPE (   '<e><e>'|| REPLACE (xml_str, ',', '</e><e>')
                                          ||'</e></e>'
                                         ).EXTRACT ('e/e')
                                )
                   ) x)
SELECT ct.val1, listagg(table2.val2,',') withingroup (orderby table2.val2) val2
  FROM changed_table ct, table2 table2
 WHERE ct.val2new = table2.val1
groupby ct.val1;

I have used the XMLTYPE to separate the comma separated numbers to rows. Then joined the rows with second table to get the description and finally used the LISTAGG function to form comma separated string. Don't know how efficient this query is. I agree with Mark Bannister's comment.

Solution 2:

In Oracle you can run this PL/SQL block and it will do all the work:

beginfor x in (
    select permission, descr from table2
  ) loop
    update table1 set permission = replace(permission, x.permission, x.descr);
  end loop;
  commit;
end/

I used this script to create a schema:

createtable table1(
  org varchar2(100),
  permission varchar2(1000)
);

createtable table2(
  permission varchar2(100),
  descr varchar2(1000)
);

insertinto table1(org, permission) values ('Company1', '1,3,7');
insertinto table1(org, permission) values ('Company2', '1,3,8');
insertinto table2(permission, descr) values ('1', 'Read');
insertinto table2(permission, descr) values ('7', 'Execute');
insertinto table2(permission, descr) values ('8', 'Delete');

Solution 3:

one of the awesome solution provided here to this sort of replace.

It just created one multiple_replace function:

CREATE TYPE t_text ISTABLEOF VARCHAR2(256);

CREATEFUNCTION multiple_replace(
  in_text IN VARCHAR2, in_old IN t_text, in_new IN t_text
)
  RETURN VARCHAR2
AS
  v_result VARCHAR2(32767);
BEGIN
  IF( in_old.COUNT <> in_new.COUNT ) THENRETURN in_text;
  END IF;
  v_result := in_text;
  FOR i IN1 .. in_old.COUNT LOOP
    v_result := REPLACE( v_result, in_old(i), in_new(i) );
  END LOOP;
  RETURN v_result;
END;

and then you could use this query to replace:

UPDATE Table1
SET permission_id = multiple_replace(Permission, (selectdistinct Permission from table2), 
        (selectdistinct Permission_name from table2));