How do I replace a pattern using T-SQL?

Question

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?

Answer 1

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'