joshweir joshweir - 4 months ago 13
MySQL Question

Replace duplicate adjacent characters with single character using mysql

Using mysql, this query:

select "-foo---baz-beer--" as result


I want to transform to replace multiple adjacent occurrences of
-
with its single counterpart. Psuedocode:

select custom_replace("-foo---baz-beer--") as result


Would return this:

-foo-baz-beer-


How do do this with mysql?

Answer

Here is a trick you can use for this purpose:

select replace(replace(replace(col, '-', '><'), '<>', ''), '><', '-')