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 ('')

Is this a one off job or something you want to do regularly? — DavidG
– DavidG, Commented Jun 9, 2015 at 14:48

collapsar · Accepted Answer · 2015-06-09 14:48:41Z

4

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.

answered Jun 9, 2015 at 14:48

collapsar

17.4k5 gold badges40 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bill Greer Over a year ago

Nice. I'm testing now. I'll be back to mark as answer once I pass all tests. :)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related