Find and correct pattern of string in SQL Server 2008

I have observed that we need to follow specific pattern in some string field. It is difficult and time consuming sometime to loop around string for check for patter and if string not found with specific pattern, update the string value.
Let us have one small example, suppose I have one string field with alphanumeric value. First numeric value should come than alphabetic value separated by hyphen sign.
eg: ((123))-Ritesh
Above should be our value. Numeric value should be surrounded by opening and closing double brackets. If brackets are not there before and after numeric value, it should be padded. How can we achieve this? There is very small T-SQL code with couple of string functions needed to perform this operation, let us have look at that.
declare @val varchar(50)
Set @val = ‘111-Ritesh’
if charindex(‘))’,@val, charindex(‘-‘,@val)-1) = 0
      set @val = stuff(@val, charindex(‘-‘,@val), 1, ‘))-‘)
if charindex(‘((‘, @val) = 0
      set @val = stuff(@val, 1, 0, ‘((‘)
select @val
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: