Oracle替换函数 [英] Oracle Replace function

问题描述

我需要在选择查询时将Table1的值替换为Table1的值。

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

例如：

表1：

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

表2：

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

我需要这样：

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

推荐答案

如果你不想更新现有的表，只想选择数据，那么你可以使用这个有点费力的查询。

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;

我使用XMLTYPE将逗号分隔的数字分隔为行。然后用第二个表加入行以获得描述，最后使用LISTAGG函数形成逗号分隔的字符串。不知道这个查询的效率如何。我同意Mark Ba​​nnister的意见。

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.

这篇关于Oracle替换函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！