Replace multiple repeating character to one

Question

I have a varchar column, and each field contains a single word, but there are random number of pipe character before and after the word.

Something like this:

MyVarcharColumn
'|||Apple|||||'
'|||||Pear|||||'
'||Leaf|'

When I query the table, I wish to replace the multiple pipes to a single one, so the result would be like this:

MyVarcharColumn
'|Apple|'
'|Pear|'
'|Leaf|'

Cannot figure out how to solve it with REPLACE function, anybody knows?

Answer 1

vkp's method absolutely solves your issue. Another method that works, and also will work in a variety of other situations, is using a triple REPLACE()

SELECT REPLACE(REPLACE(REPLACE('|||Apple|||||', '|', '><'), '<>',''), '><','|')

This method will allow you to keep a delimiter between multiple strings where Mr. VPK's method will concat the strings and put a delim at the very beginning and the very end.

SELECT REPLACE(REPLACE(REPLACE('|||Apple|||||Banana||||||||||', '|', '><'), '<>',''), '><','|')

Answer 2

One way is to replace all the | with blanks and add a pipe character at the beginning and the end of string.

select '|'+replace(mycolumn,'|','')+'|' from tablename