Help with simple Query to Update 2 new columns in

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with simple Query to Update 2 new columns in

Author

Topic

rwsjbs
Starting Member

17 Posts

Posted - 2010-01-27 : 18:08:06

Thank you for looking at my post.

I have added the following two new columns to a table named LineItemHistory:

Color
Sizes

I need help updating these two new columns with data that is already in the table in another field named ProductName.

Currently the item's description, color, and size are all stored in the existing field called ProductName. They are seperated by a comma. So a Cotton T-shirt that is White and a size M is stored in the ProductName field as:

Cotton T-Shirt,White,M

I also have other item's descriptions stored in the ProductName field that do not have a comma seperating the data. So if I have a product called Watch, it is stored in the ProductName field as:

Watch (no commas seperating the data since there is no color and size).

Here is the data in the LineItemHistory Table now:

PRODUCT NAME COLOR SIZES
Cotton T-Shirt,White,M <NULL> <NULL>
Watch <NULL> <NULL>

Here is what I would like for it to look like:

PRODUCT NAME COLOR SIZES
Cotton T-Shirt,White,M White M
Watch <NULL> <NULL>

Here is my question:

What is the query that will look at the ProductName field and see if it has two commas. If it does, copy the data after the 1st comma to the column named Color and copy the data after the 2nd comma to the column named Sizes.

If the ProductName doesn't have two commas, skip the record. No update needed since this would be an item with no color size (the Watch).

I really appreciate any help that anyone could give.

Thank you,
Richard Scott

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 02:55:09

[code]
SELECT
[PRODUCT NAME] = substring(ProductName + ',', 0 + 1, charindex(',', ProductName + ',', 0 + 1) - 0 - 1 )
, [COLOR] = substring(ProductName + ',', charindex(',', ProductName + ',') + 1, charindex(',', ProductName + ',',
charindex(',', ProductName + ',') + 1) - charindex(',', ProductName + ',') - 1 )
, [SIZES] = substring(ProductName + ',', charindex(',', ProductName + ',', charindex(',', ProductName + ',') + 1) + 1,
charindex(',', ProductName + ',', charindex(',', ProductName + ',',
charindex(',', ProductName + ',') + 1) + 1) - charindex(',', ProductName + ',',
charindex(',', ProductName + ',') + 1) - 1 )
FROM dbo.LineItemHistory
[/code]

rwsjbs
Starting Member

17 Posts

Posted - 2010-01-28 : 14:53:37

Kristen,

Thank you for sending me the query. I wanted to let you know that it correctly takes the current ProductName and seperates it out in to ProductName, Color and Sizes.

I do get an error message if the ProductName doesn't have the commas in the ProductName. An example would be if the ProductName description just says "Watch" not "Cotton T-shirt,White,M" Unfortunately we have both types of descriptions stored in the ProductName field.

Here is the error I get if the data in the ProductName field doesn't have the commas:

Database Server: Microsoft SQL Server
Version: 08.00.0194
Runtime Error: [Microsoft][ODBC SQL Server Driver][Sql Server]Invalid length paramater passed to the substring function

Here are my questions:

1. Is there a way that I can test the ProductName field for the two commas and if they are not there have the query ingnore the row? I only need the ProductName to be seperated out when there is a color and size (all of these descriptions in the ProductNumber field have have two commas in them).

2. How do I have the results (the seperated "White" and "M") of the query update the two new fields I created in the LineItemHistory file? So, I would like the Color "White" stored in the new LineItemHistory.Color field and the size "M" stored in the new LineItemHistory.Sizes field.

Thank you so much for your help.

Richard Scott

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 15:10:41

1) [SIZES] = CASE WHEN ProductName NOT LIKE '%,%,%' THEN NULL ELSE substring .... END

or just put a
WHERE ProductName LIKE '%,%,%'
to only include rows with double-cooma in product name.

2)


UPDATE U
SET [PRODUCT NAME] = substring(...
  , [COLOR] = substring(...
  , [SIZES] = substring(...
FROM LineItemHistory AS U
WHERE ProductName LIKE '%,%,%'

rwsjbs
Starting Member

17 Posts

Posted - 2010-01-29 : 08:53:05

Kristen,

It works! Thank you so much for taking the time to help me. I sincerely appreciate it!

-Richard Scott

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 09:41:08

Glad to help

rwsjbs
Starting Member

17 Posts

Posted - 2010-03-12 : 15:36:37

Thank you for looking at my post.

I have an additional question on a query that Kristen originally helped me solve.

Currently the item's description, color, and size are all stored in an existing field called ProductName. They are seperated by a comma. So a Cotton T-shirt that is White and a size M is stored in the ProductName field as:

Cotton T-Shirt,White,M

Here is my problem,I now also have new data in the same field (ProductName) that has 3 commas. So a Cotton Top for a Girl whose color is Black size Large is stored as:

Cotton Top,Girl,Black,L

I also have other item's descriptions stored in the ProductName field that do not have a comma seperating any data. So if I have a product called Watch, it is stored in the ProductName field as:

Watch (no commas seperating the data since there is no color and size).

Here is the data in the LineItemHistory Table now (before I run the query):

PRODUCT NAME COLOR SIZES
Cotton T-Shirt,White,M <NULL> <NULL>
Cotton Top,Girl,Black,L <NULL> <NULL>
Watch <NULL> <NULL>

Here is what I would like for it to look like:

PRODUCT NAME COLOR SIZES
Cotton T-Shirt,White,M White M
Cotton Top,Girl,Black,L Black L ** This ProductName has 3 commas
Watch <NULL> <NULL>

The query above works perfectly when the ProductName field has only 2 commas. Since I now have ProductNames with 2 and 3 commas, the query doesn't work on the ProductNames with 3 commas. The substring pulls the data from left to right and my 3 comma ProductName saves as:

PRODUCT NAME COLOR SIZES
Cotton Top,Girl,Black,L Girl Black

Here is my question:

Can you have a Substring pull from Right to Left? This would work for me since the SIZE is always the last item in this field and the COLOR is always the second to the last position (seperated by a ,)

I really appreciate any help or suggestions.

Thank you,
Richard Scott

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources