SQL Server 2008 - Add to string in particular posi

Question

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 not MySQL.

I have a table where I need to update specific names by adding an additional string after a certain point whilst keeping the rest of the string to the right intact.

e.g.

Current Name = 'Name - Location - 0005'
New Name = 'Name (West) - Location - 0005'

As you can see I need to add the text (West), I have a table which lists all the codes (e.g. 0005) and I need to link that into my where clause to only update the relevant names.

So the questions are:

1 - How can I update the Name by adding additional text at a set location (5th character), whilst maintaining whatever text is to the right of the names

2 - Is there a way that I can do a sort of like in to check the code, I tried using Contains however the table is not full-text indexed. This is not a massive issue as I can manually create update statement using just like for each row within the table, would just be a nice to know to add to my knowledge base.

Answer 1

Use STUFF function

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

Answer 2

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

Answer 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  @val AS Val
INTO    #temp

UPDATE  #temp
SET     Val = REPLACE(@val, 'Name ', 'Name (West) ')
WHERE   RIGHT(Val, 4) = '0005'

SELECT  *
FROM    #temp

DROP TABLE #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.