Update from Column to part of the field

Question

Update from Column to part of the field

ecomkid

SSC Veteran

Points: 221
More actions
December 9, 2007 at 11:21 pm

#181225

Using MS SQL 2005
For this example:
~/products/art-frame-direct/accessories/baskets/10484803.jpg
Using the working code (thanks Jason Selburg):
UPDATE PRODUCTS
SET pictureURL ='~/ProductImages' + RIGHT(PictureURL, LEN(pictureURL) - 47
RESULTS on one of the rows:
~/ProductImages/10484803.jpg
Problem:
15,000 products all need to be updated from the same table in the Sku column, Product_ID is the PRIMARY key:
~/ProductImages/10484803.jpg becomes ~/ProductImages/IB-A-12.jpg
The Sku's vary in length and unfortunately a few of them have a couple of '/' in them as well as '#' too.
Tried using LEFT, but no such luck as I am not sure how to get the Sku column field's info in the above query string and how to take into account the numbers.jpg vary in length as well.
Any help would be appreciated.
Thanks in Advance, Great Resource

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 1

If all the SKU's start with a numeric digit and nopne of the paths leading to the file have a digit anywhere in them, then the following demo code gives an example of what you could do... and it's nasty fast...

--===== Create a demo table... this is not part of the solution

DECLARE @Products TABLE (OLDPictureURL VARCHAR(100), NEWPictureURL VARCHAR(100))

INSERT INTO @Products (OLDPictureURL)

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

SELECT '~/products/art-frame-direct/accessories/10484804.jpg' UNION ALL

SELECT '~/products/art-frame-direct/10484805.jpg' UNION ALL

SELECT '~/products/10484806.jpg' UNION ALL

SELECT '10484807.jpg'

--===== Demo the method side by side with the orginal

UPDATE @Products

SET NEWpictureURL ='~/ProductImages/' + SUBSTRING(OldPictureURL, PATINDEX('%[0-9]%',OldPictureURL),100)

SELECT * FROM @Products

Write back if the conditions are other than what I stated...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ecomkid SSC Veteran Points: 221 More actions · Answer 2

Missed the Sku column, will try again:

UPDATE @Products SET NEWpictureURL ='~/ProductImages/' + SUBSTRING(OldPictureURL, PATINDEX('%[0-9]%',OldPictureURL),100)
SELECT * FROM @Products

Not quite sure I got that, I will provide more info:

MS SQL 2005 SP2 on Windows Server 2003 SP2

Problem:

SAMPLE DATA FROM PRODUCTS TABLE:

Product_Id------------PictureURL------------Sku
69------------~/Assets/ProductImages/1029351.jpg------------IB-A3-12
70------------~/Assets/ProductImages/129426.jpg------------IK-BC-1543-AD
71------------~/Assets/ProductImages/13426.jpg------------IK-12454-AD-S

OUTPUT TO PictureURL COLUMN:

~/Assets/ProductImages/IB-A3-12.jpg
~/Assets/ProductImages/IK-BC-1543-AD.jpg
~/Assets/ProductImages/IK-12454-AD-S.jpg
~/Assets/ProductImages/1029351.jpg

RULES:

1. The product number can vary in length

2. The Sku column can vary in length

3. The Sku column has some Sku's that have '/' and '#' in them that need to be replaced with '-'

4. The product number field needs to be partially replaced leaving everything in front of it alone

**Newbie Questions:

1. What is the best way to show rows of data here? I am sure the way I did it can add to confusion if a lot of columns of data needed to be shown.

2. How would I normally structure the info above before asking the question?

3. How would I normally structure the RULES? And would I even call them RULES?

4. How would I write the query so I could view the data before actually changing to update the table?

5. When I try and use the code with the '@' symbol on a sample table, I get a syntax error. Above: @Products. As soon as I get rid of it, it works?

This will help me not waste anyone's time and be able to structure the question(s) properly. 😀

Thanks again.

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 3

1. What is the best way to show rows of data here? I am sure the way I did it can add to confusion if a lot of columns of data needed to be shown.
2. How would I normally structure the info above before asking the question?
3. How would I normally structure the RULES? And would I even call them RULES?
4. How would I write the query so I could view the data before actually changing to update the table?
5. When I try and use the code with the '@' symbol on a sample table, I get a syntax error. Above: @Products. As soon as I get rid of it, it works?
This will help me not waste anyone's time and be able to structure the question(s) properly.

1. See the following URL...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

2. Same as above.

3. You did fine on your last post... Crystal clear examples and "rules"

4. That was the intent of my code... demo an example.

5. Are you using SQL Server? The "@" symbol, in this case, identifies a "table variable".

I'll see if I can get back to your question tonight, if no one else does... On my way to work just now...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Anam Verma SSCertifiable Points: 5365 More actions · Answer 4

Hi ecomkid

Check this based on your sample data.

DECLARE @Products TABLE

(product_id int

,OLDPictureURL VARCHAR(100)

, sku VARCHAR(100)

, NEWPictureURL VARCHAR(100))

INSERT INTO @Products (product_id, OLDPictureURL, sku)

SELECT 69, '~/Assets/ProductImages/1029351.jpg', 'IB-A3-12'

UNION ALL SELECT 70, '~/Assets/ProductImages/129426.jpg', 'IK-BC-1543-AD'

UNION ALL SELECT 71, '~/Assets/ProductImages/13426.jpg', 'IK/12454#AD-S'

SELECT * FROM @Products

UPDATE @products

SET NEWPictureURL = SUBSTRING(OLDPictureURL,1,patindex('%[0-9]%',OLDPictureURL)-1) + replace(replace(sku,'/','-'),'#','-') + '.jpg'

SELECT * FROM @Products

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 5

That should do it 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)