SQL Server Query to replace extra ";;;" characters

Question

I have following rows in my table, i want to remove the duplicate semicolons(;).

ColumnName
Test;Test2;;;Test3;;;;Test4;
Test;;;Test2;Test3;;Test4;
Test;;;;;;;;;;;;;;;;;Test2;;;;Test3;;;;;Test4;

from the above rows, i want to remove the duplicate semicolons(;) and keep only one semicolon (;)

Like below

ColumnName
Test;Test2;Test3;Test4
Test;Test2;Test3;Test4
Test;Test2;Test3;Test4

Thanks Rajesh

Answer 1

I like to use this pattern. AFAIK, originally posted by Jeff Moden on SQLServerCentral (link). I've left the terminal semi-colon in all the rows as they are single occurance items. SUBSTRING() or LEFT() to remove.

CREATE TABLE #MyTable (MyColumn VARCHAR(500))
INSERT INTO #MyTable
SELECT 'Test;Test2;;;Test3;;;;Test4;'   UNION ALL
SELECT 'Test;;;Test2;Test3;;Test4;'     UNION ALL
SELECT 'Test;;;;;;;;;;;;;;;;;Test2;;;;Test3;;;;;Test4;' 

-- Where '^|' nor its revers '|^' is a sequence of characters that does not occur in the table\field
SELECT REPLACE(REPLACE(REPLACE(MyColumn, ';', '^|'), '|^', ''), '^|', ';')
FROM #MyTable   

-- If you MUST remove terminal semi-colon
SELECT CleanText2   = LEFT(CleanText1, LEN(CleanText1)-1)
FROM
(
    SELECT CleanText1   = REPLACE(REPLACE(REPLACE(MyColumn, ';', '^|'), '|^', ''), '^|', ';')
    FROM #MyTable
)DT