Remove repeated concurrent characters in ORACLE 10g

Question

I have an aggregated varchar2 column, containing scans done, in Oracle 10g. An example being:

My_Column
XXXOABBOABBBBOABBBOABBBOABBBOABBBOOABUXXX

I need to remove repeated concurrent characters, viz. I need to convert above string to:

My_Column
XOABOABOABOABOABOABOABUX

I need the relative order of each character to be conserved How can I do that?

I'm thinking of using nested REPLACE() functions to clean the string but I'm looking for a less messier alternative

Answer 1

You could use REGEXP_REPLACE.

For example,

SQL> WITH DATA(str) AS(
  2  SELECT 'XXXOABBOABBBBOABBBOABBBOABBBOABBBOOABUXXX' FROM dual
  3  )
  4  SELECT str, regexp_replace(str, '(.)\1+','\1') new_str FROM DATA;

STR                                       NEW_STR
----------------------------------------- ----------------------------
XXXOABBOABBBBOABBBOABBBOABBBOABBBOOABUXXX XOABOABOABOABOABOABOABUX

SQL>