How to use Replace an unknown length of characters in SQL Server?

Question

How do I replace a series of periods in SQL server ? If a field has more than one period next to each other, then I want to replace them with asterisks. The problem is that I want to do this with 2,3,4... asterisks. I'll never know how many periods there will be. Some users enter two and some enter ten.

When to replace or delete:

• When there is more than one period next to each other I want to replace them with asterisks.

• When a period is the only character in the field I want to delete it.

My Select statement is as follows:

SELECT CAST(QTEXT + ' ' AS VARCHAR(MAX))FROM MYTABLE WHERE CMPNO = @compID AND Q5XY = 'NDT' FOR XML PATH ('')

Answer 1

Replace the periods pairwise and adjust for the case that there are an odd number of periods:

 select replace ( replace (replace (qtext, '..', '**'), '*.', '**' ), '.', '')
   from ...
      ;

Single periods (= without adjacent periods) have remained in place after the first two replacements and are deleted by the third replace call.