How to do REPLACE in SQL Queries

Hi,

I am having a situation where I need to update a column in my SQL table that contains a link to an image file. Basically ...

I have this stored in a column IMAGESRC

Project/aa11be5d-dd9e-48c8-9d8c-6a972e996b28/ProjectImages/702d_2.jpg

I need to change it to this

Project/NEWUSERID/ProjectImages/702d_2.jpg

How can I accomplish this in SQL?

thanks in Advance

Dollarjunkie

http://msdn2.microsoft.com/en-us/library/ms186862.aspx

http://www.sqlteam.com/article/using-replace-in-an-update-statement

Update table set IMAGESRC = Replace(IMAGESRC, 'aa11be5d-dd9e-48c8-9d8c-6a972e996b28', 'NEWUSERID')

|||

Hello Dollarjunkie,

In SQL you have two functions you can use:

- CharIndex to locate the position of the first and second '/'

- Substring to split your string into parts that you can concatenate to a new string.

In your case it will be something like:

... Substring(IMAGESRC, 1, CharIndex(IMAGESRC, '/', 1) + 1) + NEWUSRID +

Substring(IMAGESRC, CharIndex(IMAGESRC, '/', CharIndex(IMAGESRC, '/', 1) + 1))

The first substring results in: Project/

The second substring results in: /ProjectImages/702d_2.jpg

Hope this helps.

Jeroen Molenaar.