How do I replace a pattern using T-SQL?

design-patterns, replace, sql

Solution

Yes you are correct you can use PATINDEX

Declare @searchstring varchar(50)

Set @searchstring = 'POB9090'

If (PatIndex('POB[0-9]%', @searchString) > 0)
Begin

Set @searchstring = Replace(@searchString, 'POB', 'POB ')

End

Select @searchString

Or probably a nicer way would be to use a `case` statement so that it can be easily incorporated in to a `select` statement

Declare @searchstring varchar(50)

Set @searchstring = 'POB9090'

Select Case
        When PatIndex('POB[0-9]%', @searchString) > 0 Then Replace(@searchString, 'POB', 'POB ')
        Else @searchString
        End 'SearchString'

Problem

I have code to normalize a POB address. For example, one of the normalizations included is: ``` set @string = replace(@string, 'pobox', 'pob') ``` Now I want to do something similar: I want to find any POB that is directly followed by a number (without a space in between) and insert a space. I want to find the pattern `like POB[0-9]` and then replace the "POB" with "POB ". How can I accomplish this? Can it be done with a simple replace? Or do I need to use some other function, like `PATINDEX`?

Original source