CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Bill Greer
Bill Greer

Reputation: 3166

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

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:

My Select statement is as follows:

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

Upvotes: 1

Views: 789

Answers (1)

collapsar
collapsar

Reputation: 17238

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.

Upvotes: 4

Related Questions