I have a string in Oracle DB in the format 'a|b|c' , '|' being the separator between characters. Want to write an SQL query to transform it into a string in the format 'a,b&c'. First occurrence of '|' to ',' 2nd occurrence to '&'.

If suppose the string comes in the format 'a|b' then output should be 'a&b'.

I'm using multiple regex_replace queries to achieve this right now.

 select REGEXP_REPLACE ('a|b|c', '[|]', ',', 1, 1)
    from dual

Is there any other solution using one single query?

CodePudding user response：

Nested replaces (see line #6):

SQL> with test (col) as
  2    (select 'a|b|c' from dual union all
  3     select 'a|b'   from dual
  4    )
  5  select col,
  6    regexp_replace(regexp_replace(col, '\|', ',', 1, 1), '\|', '&', 2, 1) result
  7  from test;

COL   RESULT
----- --------------------
a|b|c a,b&c
a|b   a,b

SQL>

CodePudding user response：

If you want to replace the last | in the list with & and all the preceding |s with , then you can use:

SELECT value,
       REGEXP_REPLACE(
         REGEXP_REPLACE( value, '\|([^|]*)$', '&\1' ),
         '\|',
         ','
       ) AS replaced
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT 'A|B|C|D' FROM DUAL UNION ALL
SELECT 'A|B|C'   FROM DUAL UNION ALL
SELECT 'A|B'     FROM DUAL UNION ALL
SELECT 'A'       FROM DUAL;

Outputs:

fiddle