How do I replace a pattern using T-SQL?
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 开发者_Python百科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
?
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'
精彩评论