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