How to replace one or more consecutive symbols with one symbol in DB2

I am using DB2 LUW 9.5. In a field, I have a value like this one:

Test^test^^test^^^^test^^test^test

In a SELECT query, I would like to replace the duplicated ^ with only one ^. This would produce:

Test^test^test^test^test^test

The delimiter is known and static (ca开发者_Go百科n be hardcoded). Would you know a way to obtain the desired output using DB2 functions?

Thank you

You need one other character that can be used as delimiter, for example the pipe sign (|).

Let's say the table is defined as

create table myTable (
    myColumn varchar(400) 
);

Add a value for a test:

insert into myTable (myColumn) values
    ('Test^^^^^^^^test^^^^^^^test^^^^^^test^^^^^test^^^^test^^^test^^test^test');

Then do a smart replacement with use of the other delimiter

select replace(replace(replace(myColumn, '^^', '^|^'), '|^^', ''), '^|^', '^') 
    from myTable;

The result:

Test^test^test^test^test^test^test^test^test^test

Instead of using a one character delimiter you can use a string of which you are sure it will not occur in the values, for example 'xy'. The next query will give the same results:

select replace(replace(replace(myColumn, '^^', '^xy^'), 'xy^^', ''), '^xy^', '^') 
    from myTable;