swapping comma separated values in oracle

Question

I have a table with one of its column having comma separated values, ( for example: longitude,latitude,longitude1,latitude1,etc)..

Now I need to swap the values like ( latitude,longitude,latitude1,longitude1,etc).

As for a trial purpose: I have created a table as follows:

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;
/

Now the table looks like:

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

I need to update the swapped values into the new_string column, and the result should looks like:

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

What I have done so far is using PL/SQL code using COLLECTION as follows, and is working fine:

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;
/

I think this solution is very lengthy, is there any other good/short/easy method to swap values for the expected result?

Thanks in advance ;)

Don't use `;` **and** `/` for regular SQL statements like `CREATE TABLE` or `COMMIT`: http://stackoverflow.com/a/10207695/330315 — , Nov 04 '14 at 08:53

score 5 · Answer 1 · answered Nov 04 '14 at 08:46

You can use the connect by syntax to split your comma-separated lists into their separate elements, and put them back together in a different order, all in plain SQL. The two slightly tricky bits are swapping the pairs over, which can be done by adjusting each position up or down by one depending on whether it's odd or even; and applying this syntax to multiple rows of data at once, which can be done with a trick using a deterministic function:

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

You can then use that as the using clause of a merge to update the original table:

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 including what the inner query produces.

If you need to use this more generically you could create a function instead:

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 Fiddle.

Of course it isn't a good idea to store comma-separated values in a column in the first place; each value should be its own column, in a child table if you have multiple pairs. If you're adding a new column I'd really seriously consider a revamp of the data model instead. Sometimes you're stuck with what you have though, and even if you can split the data out, this sort of technique can be useful for doing that one-off exercise too.

Ach, snap. I must have started writing mine just before you posted :-(. — Ben, Nov 04 '14 at 09:08
@Ben - your odd/even approach is slightly different though, and better-explained... you deleted before I could up-vote *8-) — Alex Poole, Nov 04 '14 at 09:12
I don't think there's that much difference but thanks. I'll undelete so that if there is enough of a difference in explanation it might help someone. — Ben, Nov 04 '14 at 09:37
@AlexPoole, Thanks for your effort, and I am learning many from your answers. And about the table structure, it was not the direct table I am handling, this is just a mediator handling table. I am aware about the problems while storing comma separated values in a single column:).. Thanks again for teaching me lot in oracle, and I hope Ben's reverse_string function is better. — ajmalmhd04, Nov 04 '14 at 10:10
@ajmalmhd04 - our two functions are just applying the `mod` in different places, they're pretty much the same approach (apart from the `+`!). If Ben's is clearer then that's OK - that can count as better. — Alex Poole, Nov 04 '14 at 10:20
yeah, I tried both.. both answers well, If I could share I would accept both answers , anyway I've upvoted yours too ;) — ajmalmhd04, Nov 04 '14 at 10:28

Ben · Answer 2 · 2014-11-04T10:29:58.190

No, there's not. This is why normalisation is so important, if you had a table that looked like this then you could aggregate the strings any way you wanted for output:

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

However, whilst there's not a short or easy way of doing this there's a more comprehensible one. The order of the data you want output is described by the following statement. The important point here (and the main difference to yours) is the ORDER BY. MOD(LEVEL, 2) returns 0 for even levels and and 1 for odd. By adding LEVEL to this you end up with each consecutive pair described by an even number, i.e. 1 & 2 (or a and b) will be 2 and 3 & 4 will be 4. Then, ordering by LEVEL again gives us the highest first, which swaps each pair. I include each of these columns for convenience.

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.

You can then use LISTAGG() to re-aggregate. This includes an ORDER BY clause so you don't have to have an explicit ORDER BY in a sub-select.

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>

If we put all of this in a function (so that you don't perform hierarchical queries on the maximum number of items in each list but in the correct number for each) then you get a single returned value per input string:

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;

This can be demonstrated with a simple SELECT statement:

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

Lastly, this means that an UPDATE will suffice for updating your table. This means you can perform it in a single transaction without the need for loops etc.

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

Thanks @Ben, the reverse_string function with pattern as '[^,]+' would give better result for string like 'ab,cd' etc. — ajmalmhd04, Nov 04 '14 at 10:10
Yup, you're right... I would normally do that so don't know what came over me - thanks. — Ben, Nov 04 '14 at 10:30
@Eat Å Peach posted a good answer, I think I should accept his answer. — ajmalmhd04, Nov 06 '14 at 07:19

Noel · Accepted Answer · 2014-11-06T07:28:03.227

Using only 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

Pattern:

([^,]+) -- 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.

So, this pattern matches two strings separated by a comma.

Replace_String:

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

So, this replaces the matched pattern with the same string, but interchanging the position.

Seems very simple in your answer!!, I am a beginner in regular expression, I would appreciate if you can little bit explain how the replacing patters works. — ajmalmhd04, Nov 06 '14 at 06:42

swapping comma separated values in oracle

3 Answers3