Need help with a simple query

Hi Everyone:

I am new to SQL, and I would like to request if someone could provide me with a query that could do a find and replace in a column in MSDE. I have an image path column in a table, the current image path is set to /images/xxxx.gif

I would like to add another folder so the image

path looks like this --> /images/imagesA/xxxx.gif

The problem is i have 4500 records to update, so I cannot do this manually, so my question is can someone provide me with a query that can do this find and replace, i guess in this situation you are finding /images and replacing it with /images/imagesA

Please advise. Thanks.

It would be nice if you can send me this query to my email. My email address is aleemmansoor@hotmail.com

Answer this question

Need help with a simple query

mr anonymous

Hi pete:

Thank you for responding to my problem. I ran the query, and it basically runs and then pops up an error. It also does insert the Expression 3 but for some reason it repeats it. Following is the query i am running, the results and the error i received. Any advice would be helpful.

Error:

Error Num. -2147220991 - RecordSet Status: not open

Query:

UPDATE bvc_ProductImage
SET FileName = REPLACE(FileName,'products','products\imagesA')
WHERE ProductID = 'L_HV303'

Result:

images\products\imagesA\imagesA\hv303_jec8430_cob_225.jpg

If you notice in the result, imagesA is being inserted twice. What am I doing wrong in this picture Please help

Mansoor Aleem
A. Mereaux

Use the SQL Server "REPLACE" function, so for your situation the query would look like:

UPDATE {YourTable}

SET col1 = REPLACE(col1,'/images','/images/imagesA')

See http://msdn.microsoft.com/library/default.asp url=/library/en-us/tsqlref/ts_ra-rz_76lh.asp for more information on REPLACE.

Pete
rkbrown

What Peter said is correct. I tested it and it works as expected.

Could you let us know what are the initial values in your table (bvc_ProductImage) for the column (FileName).

Execute the following lines to see how it works:

--create table

create table test1

(

imgName char(100),

imgNo int

)

--insert values

insert into test1

values('images\products\hv303_jec8430_cob_225.jpg',1)

insert into test1

values('images\products\hv305_jec8430_cob_225.jpg',1)

---display values in table

select * from test1

--then update table with replace command

update test1

set imgName=REPLACE(imgName,'products','products\imagesA')

Then do a Select * from test1 which displays

images\products\imagesA\hv303_jec8430_cob_225.jpg          1

images\products\imagesA\hv305_jec8430_cob_225.jpg    2       , which is as expected.

Hope this helps!

Jagan
Need help with a simple query

Answer this question