Sql Server 2008 - Add To String In Particular Position Within Name Column

I have been asked to do a job which is beyond my SQL skills a little and having done some research online, cannot quite find the write solution to be done in SQL Server 2008 and no

Solution 1:

with the help of STUFF we can achieve this

STUFF ( character_expression , start , length , replaceWith_expression )
SELECT STUFF('Name - Location - 0005', 5, 0, ' (West)');

output would be Name (West) - Location - 0005

Solution 2:

Use STUFF function

declare@namesvarchar(100)
set@names='Name - Location - 0005'select stuff(@names,5,0,' (West)')

Solution 3:

You can use simple string replacement to insert the (West) like so:

DECLARE@val NVARCHAR(30) ='Name - Location - 0005'SELECT REPLACE(@val, 'Name ', 'Name (West) ')

Taking this a step further to perform an update with a WHERE clause to filter records ending with '0005', you can do this:

DECLARE@val NVARCHAR(30) ='Name - Location - 0005'SELECT@valAS Val
INTO    #temp

UPDATE  #temp
SET     Val = REPLACE(@val, 'Name ', 'Name (West) ')
WHERERIGHT(Val, 4) ='0005'SELECT*FROM    #temp

DROPTABLE #temp

This assumes the format is consistent. You may need to tweak it if Name can appear more than once or if the 0005 can appear in positions other than the end of the value.