Update Partial Field

Question

Post reply

Update Partial Field

ecomkid

SSC Veteran

Points: 221
More actions
December 9, 2007 at 3:30 pm

#184688

Problem
I have this SQL 2005 database with a PRODUCTS table which has over 15,000 rows and all have the same part of a path for a product image url that will no longer will work.
How would I write the query to update only the column, ImageUrl, partially in each of row of the product?
In one of the rows:
~/products/art-frame-direct/accessories/baskets/10484803.jpg
Want it to be:
~/ProductImage/10484803.jpg
I need the
~/
and
product_number.jpg
to be there along with the path replaced.
Your help is appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

You can just use the update part below, but the rest is there to show you that it works.

DECLARE @sampleTable TABLE (pictureURL VARCHAR(100))

INSERT @sampleTable

SELECT '~/products/art-frame-direct/accessories/baskets/10484803.jpg'

UNION ALL

SELECT '~/products/art-frame-direct/accessories/baskets/10484873.jpg'

UNION ALL

SELECT '~/products/art-frame-direct/accessories/baskets/88884873.jpg'

UNION ALL

SELECT '~/products/art-frame-direct/accessories/baskets/99984873.jpg'

SELECT * FROM @sampleTable

UPDATE @sampleTable

SET pictureURL = '~/ProductImage' + RIGHT(pictureURL, LEN(pictureURL) - 47)

SELECT * FROM @sampleTable

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

ecomkid SSC Veteran Points: 221 More actions · Answer 2

What does the -47 do? I am trying to figure out if I wanted to modify what to remove?

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

It's taken the right part of the field minus 47 characters (the width of the text you want removed)

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

ecomkid SSC Veteran Points: 221 More actions · Answer 4

PERFECT!!!

I did one row using the

WHERE Product_Id =

statement. I then made the modifications and checked my results until the desired path was created....gulped, and did it to all 15,000 products :w00t:

ecomkid SSC Veteran Points: 221 More actions · Answer 5

Modification:

Now, how would I UPDATE using that query now, but on the LEFT side?

e.g.

~/Assets/ProductImages/10392351.jpg

Want to UPDATE the PRODUCTS table column, UrlImage, from the Sku column in the same table to be:

~/Assets/ProductImages/PH-SW-ASW-800002BS.jpg

.jpg not being in the Sku column

Thank You

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 6

Well, it's past my bed time. Just gave up on watching the Colts vs. Ravens... GO COLTS!

I can't help you tonight, but play around on your development bax. Look into LEFT, RIGHT, and CHARINDEX in books Online.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Darrin Blunt SSC Enthusiast Points: 144 More actions · Answer 7

Try the REPLACE command.

UPDATE [your_table]

SET [your_field] = REPLACE([your_field],'products/art-frame-direct/accessories/baskets/', '')

WHERE ...