Update from Column to part of the field

Question

Post reply

Update from Column to part of the field

ecomkid

SSC Veteran

Points: 211
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: 1000539 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)
Intro to Tally Tables and Functions

ecomkid SSC Veteran Points: 211 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: 1000539 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://qa.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)
Intro to Tally Tables and Functions

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: 1000539 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)
Intro to Tally Tables and Functions