Oracle Replace function

问题:

I need to replace the Table1's filed values from Table2's values while select query.

Eg:

Table1:

Org                  Permission
--------------------------------------
Company1             1,3,7
Company2             1,3,8

Table2:

Permission          Permission
--------------------------------------
1                   Read
3                   Write
7                   Execute
8                   Delete

I need like this:

Org                  Permission
--------------------------------------
Company1             Read,Write,Execute
Company2             Read,Write,Delete

回答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,',') within group (order by table2.val2) val2
  FROM changed_table ct, table2 table2
 WHERE ct.val2new = table2.val1
group by 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.

回答2:

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

begin
  for 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:

create table table1(
  org varchar2(100),
  permission varchar2(1000)
);

create table table2(
  permission varchar2(100),
  descr varchar2(1000)
);

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

回答3:

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

It just created one multiple_replace function:

CREATE TYPE t_text IS TABLE OF VARCHAR2(256);

CREATE FUNCTION 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 ) THEN
    RETURN in_text;
  END IF;
  v_result := in_text;
  FOR i IN 1 .. 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, (select distinct Permission from table2), 
        (select distinct Permission_name from table2));