sql - Remove repeated concurrent characters in ORACLE 10g -

i have aggregated varchar2 column, containing scans done, in oracle 10g. example being:

my_column xxxoabboabbbboabbboabbboabbboabbbooabuxxx 

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

my_column xoaboaboaboaboaboaboabux 

i need relative order of each character conserved how can that?

i'm thinking of using nested replace() functions clean string i'm looking less messier alternative

you use regexp_replace.

for example,

sql> data(str) as(   2  select 'xxxoabboabbbboabbboabbboabbboabbbooabuxxx' dual   3  )   4  select str, regexp_replace(str, '(.)\1+','\1') new_str data;  str                                       new_str ----------------------------------------- ---------------------------- xxxoabboabbbboabbboabbboabbboabbbooabuxxx xoaboaboaboaboaboaboabux  sql>