update all the fields at once in sql server

I have a situation where i wanted to apply url field of a table in DB开发者_开发问答. For eg.. all the URL's are of format

  ~/production/Class_ext/MgrLoginChoices.asp?menu1=Instruct

Now I need to change them to the format

 ~/Secure/production/Class_ext/MgrLoginChoices.asp?menu1=Instruct

I just need to add a Secure folder before everything. I dont want to do it manually. Is there a way i can update with the query.. Please help me out.. Thanks.

one way

update Table
SET URL = replace(URL,'~','~/Secure')
WHERE URL LIKE '~%'

Remember that will change all the ~, if you have more than 1 ~ in the column, then use the method below which uses STUFF

you can also use STUFF

update Table
SET URL = STUFF(URL,3,0,'Secure/')
WHERE URL LIKE '~%'

example that you can run

DECLARE @v VARCHAR(100)
SELECT @v = '~/production/Class_ext/MgrLoginChoices.asp?menu1=Instruct'

SELECT @v, STUFF(@v,3,0,'Secure/')

update urls
set url = '~/secure' + right(url, len(url) -1);