Replace char at even places in a string

August 1, 2022

Input String:

522#4#27571#0#46#2#900#1
909#1#4#0#6#2
902#1#121#2#34562#0
902#1#15#2#14#0
1659224789#3#1659224869#2#1659225532#3#1659225592#2
1659225562#1#1659225592#2

I want to replace or select every # which has occurred at even places. when replacing it with ‘,’ the output should be like:

522#4,27571#0,46#2,900#1
909#1,4#0,6#2
902#1,121#2,34562#0
902#1,15#2,14#0
1659224789#3,1659224869#2,1659225532#3,1659225592#2
1659225562#1,1659225592#2

please provide solution using aws redshift

>Solution :

I have an answer with MySQL and Oracle SQL. U can use this Regexp_Replace with backreferences.

SQL>

 SELECT WORD,REGEXP_REPLACE (WORD,'([#])([0-9])([#])','\1\2,')
      AS RESULT
 FROM 
     (
      SELECT '522#4#27571#0#46#2#900#1' AS WORD FROM DUAL UNION ALL
      SELECT '902#1#121#2#34562#0' AS WORD FROM DUAL UNION ALL
      SELECT '902#1#15#2#14#0' AS WORD FROM DUAL UNION ALL
      SELECT '1659224789#3#1659224869#2#1659225532#3#1659225592#2' AS WORD FROM DUAL
     )