CODEWITHSUNDEEP
sqloracle-database
Felipe Vidal
Felipe Vidal

Reputation: 505

REGEXP_REPLACE to replace specific duplicated characters

I'm struggling with the usage of REGEXP_REPLACE. Currently I'm trying to work on a string similar to the following one:

ABC|||CDE|||||DEF||||||556|||.|.|

What I'm trying to achieve is to have only one pipe per group. Hence the objective string is:

ABC|CDE|DEF|556|.|.|

Can this be done using REGEXP_REPLACE or a similar function? How? Notice I won't have control of the position of the characters in the string, & that there won't be a pattern (e.g. "every five characters there are pipes"). I just need to remove immediate pipe duplicates.

Upvotes: 0

Views: 683

Answers (2)

Littlefoot
Littlefoot

Reputation: 143103

OF COURSE regular expressions are prettier, but - for amusement, to show that good, old REPLACE does the job.

SQL> select
  2    replace(
  3      replace(
  4        replace('ABC|||CDE|||||DEF|||||||556||||.|.|',      --> input string
  5                '|',
  6                '|' || chr(9)
  7               ),
  8              chr(9) || '|'
  9             ),
 10             chr(9), '') result
 11  from dual;

RESULT
--------------------
ABC|CDE|DEF|556|.|.|

SQL>

Upvotes: 2

Serg
Serg

Reputation: 22811

Use REGEXP_REPLACE(value,'(\|)+','|') . For example

SELECT REGEXP_REPLACE('ABC|||CDE|||||DEF|||||||556||||.|.|','(\|)+','|') newval
FROM dual

Upvotes: 4

Related Questions