Update from Column to part of the field

  • 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

  • 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)

  • 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.

  • 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)

  • 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

  • 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)

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

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