交换预言机中的逗号分隔值

Question

我有一个表，其中一列具有逗号分隔的值，(例如：经度，纬度，经度1，纬度1等(。

现在我需要交换诸如(纬度，经度，纬度1，经度1等(之类的值。

至于试用目的：我创建了一个表，如下所示：

CREATE TABLE string_table
     (
          slno       NUMBER,
          old_string VARCHAR2(50),
          new_string VARCHAR2(50)
     );
/
INSERT INTO STRING_TABLE (SLNO, OLD_STRING)
       VALUES (1, '1,2,3,4,5,6');
INSERT INTO STRING_TABLE (SLNO, OLD_STRING)
       VALUES (2, '1,2,3,4,5');
INSERT INTO STRING_TABLE (SLNO, OLD_STRING)
       VALUES (3, 'a,b,c,d,e,f');
INSERT INTO STRING_TABLE (SLNO, OLD_STRING)
       VALUES (4, 'a,b,c,d,e');
COMMIT;
/

现在，该表如下所示：

slno  old_string  new_string                                         
----- ----------------------
1    1,2,3,4,5,6                                                                                           
2    1,2,3,4,5                                                                                             
3    a,b,c,d,e,f                                                                                           
4    a,b,c,d,e    

我需要将交换的值更新到new_string列中，结果应如下所示：

slno  old_string  new_string                                         
----- ----------------------
1    1,2,3,4,5,6    2,1,4,3,6,5
2    1,2,3,4,5      2,1,4,3,5
3    a,b,c,d,e,f    b,a,d,c,f,e
4    a,b,c,d,e      b,a,d,c,e

到目前为止，我所做的是使用 PL/SQL 代码使用 COLLECTION 如下，并且工作正常：

SET serveroutput ON
DECLARE
TYPE my_type IS TABLE OF VARCHAR2(50);
     my_obj my_type := my_type();
     l_temp_var VARCHAR2(50);
     l_string   VARCHAR2(200);
BEGIN
     FOR i IN
     ( SELECT slno, old_string FROM string_table
     )
     loop
          FOR j IN
          (SELECT regexp_substr(i.old_string,'[^,]+',1, LEVEL) val
          FROM dual
               CONNECT BY regexp_substr(i.old_string, '[^,]+', 1, LEVEL) IS NOT NULL
          )
          loop
               my_obj.EXTEND;
               my_obj(my_obj.LAST) := j.val;
               IF mod(my_obj.count,2)= 0 THEN
                    l_temp_var := my_obj(my_obj.LAST -1);
                    my_obj(my_obj.LAST-1) := my_obj(my_obj.LAST) ;
                    my_obj(my_obj.LAST):= l_temp_var;
               END IF;
          END LOOP;
          FOR i IN my_obj.FIRST..my_obj.LAST
          loop
               l_string := l_string||my_obj(i)||',';
          END loop;
          l_string := substr(l_string , 1, length(l_string)-1);
          update string_table 
          SET new_string = l_string 
          WHERE slno = i.slno;
          l_string := NULL;
          my_obj   := my_type();
     END loop;
COMMIT;
END;
/

我认为这个解决方案非常冗长，有没有其他好的/简短的/简单的方法来交换预期结果的值？

提前致谢;)

Answer 1

您可以使用

connect by语法将逗号分隔的列表拆分为单独的元素，然后以不同的顺序将它们重新组合在一起，所有这些都采用纯 SQL。两个稍微棘手的部分是交换货币对，这可以通过将每个位置向上或向下调整一个位置来完成，具体取决于它是奇数还是偶数;并将此语法一次应用于多行数据，这可以通过使用确定性函数的技巧来完成：

select slno, old_string,
  listagg(item, ',') within group (order by new_pos) as new_string
from (
  select slno, old_string, regexp_substr(old_string, '[^,]+', 1, level) as item,
    case when mod(level, 2) = 1 then level + 1
      else level - 1 end as new_pos
  from string_table
  connect by level <= regexp_count(old_string, '[^,]+')
  and prior slno = slno
  and prior sys_guid() is not null
)
group by slno, old_string;
      SLNO OLD_STRING           NEW_STRING         
---------- -------------------- --------------------
         1 1,2,3,4,5,6          2,1,4,3,6,5          
         2 1,2,3,4,5            2,1,4,3,5            
         3 a,b,c,d,e,f          b,a,d,c,f,e          
         4 a,b,c,d,e            b,a,d,c,e            

然后，您可以将其用作更新原始表的merge的using子句：

merge into string_table st
using (
  select slno, old_string,
    listagg(item, ',') within group (order by new_pos) as new_string
  from (
   select slno, old_string,
     regexp_substr(old_string, '[^,]+', 1, level) as item,
     case when mod(level, 2) = 1 then level + 1
       else level - 1 end as new_pos
   from string_table
   connect by level <= regexp_count(old_string, '[^,]+')
   and prior slno = slno
   and prior sys_guid() is not null
  )
  group by slno, old_string
) tmp
on (tmp.slno = st.slno)
when matched then
update set st.new_string = tmp.new_string;
select * from string_table order by slno;
      SLNO OLD_STRING           NEW_STRING         
---------- -------------------- --------------------
         1 1,2,3,4,5,6          2,1,4,3,6,5          
         2 1,2,3,4,5            2,1,4,3,5            
         3 a,b,c,d,e,f          b,a,d,c,f,e          
         4 a,b,c,d,e            b,a,d,c,e            

SQL Fiddle，包括内部查询产生的内容。

如果你需要更通用地使用它，你可以创建一个函数：

create or replace function swap_pairs (p_old_string varchar2)
return varchar2 as
  l_new_string string_table.new_string%type;
begin
  select listagg(item, ',') within group (order by new_pos)
  into l_new_string
  from (
   select regexp_substr(p_old_string, '[^,]+', 1, level) as item,
     case when mod(level, 2) = 1 then level + 1
       else level - 1 end as new_pos
   from dual
   connect by level <= regexp_count(p_old_string, '[^,]+')
  );
  return l_new_string;
end;
/
update string_table set new_string = swap_pairs(old_string);

SQL 小提琴。

当然，首先将逗号分隔的值存储在一列中并不是一个好主意;如果你有多个对，每个值都应该是它自己的列，在子表中。如果您要添加新列，我真的会认真考虑对数据模型进行改造。有时你会坚持你所拥有的东西，即使你可以把数据分开，这种技术对于做一次性练习也很有用。

Answer 2

不，没有。这就是为什么规范化如此重要的原因，如果你有一个看起来像这样的表，那么你可以按照你想要的任何方式聚合字符串进行输出：

create table string_table (
     slno number
   , position number
   , string varchar2(50)
     );

然而，虽然没有一种简短或简单的方法可以做到这一点，但有一种更易于理解的方法。以下语句描述了要输出的数据的顺序。这里重要的一点(以及与您的主要区别(是 ORDER BY。 MOD(LEVEL, 2) 对于偶数级别返回 0，对于奇数级别返回 1。通过添加LEVEL，你最终得到每个连续的对，用偶数描述，即1和2(或a和b(将是2,3和4将是4。然后，按 LEVEL 再次排序，首先给我们最高的，交换每对。为方便起见，我包括了这些列中的每一个。

SQL>  select regexp_substr('1,2,3,4,5,6', '[^,]+', 1, level) as str
  2        , mod(level, 2) as ml
  3        , level + mod(level, 2) as lml
  4        , level as l
  5     from dual
  6  connect by regexp_substr('1,2,3,4,5,6', '[^,]+', 1, level) is not null
  7    order by level + mod(level, 2), level desc;
STR   ML  LML    L
--- ---- ---- ----
2      0    2    2
1      1    2    1
4      0    4    4
3      1    4    3
6      0    6    6
5      1    6    5
6 rows selected.

然后，您可以使用LISTAGG()重新聚合。这包括一个 ORDER BY 子句，因此您不必在子选择中具有显式的 ORDER BY。

SQL>  select listagg(str, ',') within group (order by lvl + mod(lvl, 2), lvl desc)
  2     from ( select regexp_substr('1,2,3,4,5,6', '[^,]+', 1, level) as str, level as lvl
  3              from dual
  4           connect by regexp_substr('1,2,3,4,5,6', '[^,]+', 1, level) is not null
  5                   );
LISTAGG(STR,',')WITHINGROUP(ORDERBYLVL+MOD(LVL,2),LVLDESC)
--------------------------------------------------------------------------------
2,1,4,3,6,5
SQL>

如果我们把所有这些都放在一个函数中(这样你就不会对每个列表中的最大项目数执行分层查询，而是对每个项目的正确数量执行分层查询(，那么你会得到每个输入字符串的一个返回值：

create or replace function reverse_string (PInput in varchar2) return varchar2 is
   /* Reverse each pair of items in a comma delimited list
      */
   l_output string_table.old_string%type;
begin
   select listagg(str, ',') within group (order by lvl + mod(lvl, 2), lvl desc)
     into l_output
     from ( select regexp_substr(PInput, '[^,]+', 1, level) as str, level as lvl
              from dual
           connect by regexp_substr(PInput, '[^,]+', 1, level) is not null
                   );
   return l_output; 
end reverse_string;

这可以通过一个简单的 SELECT 语句来演示：

SQL> select slno, old_string, reverse_string(old_string) as new_string
  2    from string_table;
      SLNO OLD_STRING      NEW_STRING
---------- --------------- ---------------
         1 1,2,3,4,5,6     2,1,4,3,6,5
         2 1,2,3,4,5       2,1,4,3,5
         3 a,b,c,d,e,f     b,a,d,c,f,e
         4 a,b,c,d,e       b,a,d,c,e

最后，这意味着 UPDATE 足以更新您的表。这意味着您可以在单个事务中执行它，而无需循环等。

SQL> update string_table
  2     set new_string = reverse_string(old_string);
4 rows updated.
SQL>
SQL> select *
  2    from string_table;
      SLNO OLD_STRING      NEW_STRING
---------- --------------- ---------------
         1 1,2,3,4,5,6     2,1,4,3,6,5
         2 1,2,3,4,5       2,1,4,3,5
         3 a,b,c,d,e,f     b,a,d,c,f,e
         4 a,b,c,d,e       b,a,d,c,e

Answer 3

仅使用 regexp_replace，

with string_table(slno, old_string)
as (
        select 1, '1,2,3,4,5,6' from dual union all
        select 2, '1,2,3,4,5' from dual union all
        select 3, 'a,b,c,d,e,f' from dual union all
        select 4, 'a,b,c,d,e' from dual
)
select
        slno,
        old_string,
        regexp_replace(old_string,'([^,]+),([^,]+)','2,1')    new_string
from 
        string_table;
      SLNO  OLD_STRING   NEW_STRING
----------  -----------  ------------------------------------------------------------
         1  1,2,3,4,5,6  2,1,4,3,6,5
         2  1,2,3,4,5    2,1,4,3,5
         3  a,b,c,d,e,f  b,a,d,c,f,e
         4  a,b,c,d,e    b,a,d,c,e

模式：

([^,]+) -- any string without a comma. Enclosed in brackets to form first capture group.
,       -- a comma
([^,]+) -- any string without a comma. Enclosed in brackets to form second capture group.

因此，此模式匹配由逗号分隔的两个字符串。

Replace_String：

2  -- the second capture group from the Pattern
,   -- a comma
1  -- the first capture group from the Pattern

因此，这会用相同的字符串替换匹配的模式，但交换位置。