select with 'group by' on a comma delimited field

The following is a subset of records from a single nvarchar field in a SQL Server 2000 database that I want to summarize based on the shipment method found immediately between the first and second commas of each line.

CUSTOM,Standard US Mail,6.5,0
CUSTOM,US Priority Mail w/Conf ,7.25,0
CUSTOM,FedEx by Thursday Afternoon,12,0
CUSTOM,FedEx by Friday Afternoon,12,0
USPS,USPS Airmail Parcel Post ,33.5,0
CUSTOM,FedEx by Friday Afternoon,19,0
CUSTOM,Standard US Mail,6.5,0


I am looking for a simple select statement that given the single nvarchar field records above will extract the shipment method and count the number of their occurrences; resulting in this example with the following two fields of data.

COUNT      SHIPMENT METHOD

2      FedEx by Friday Afternoon
1      FedEx by Thursday Afternoon
2      Standard US Mail
1      US Priority Mail w/Conf
1      USPS Airmail Parcel Post
bbramanAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Anthony PerkinsConnect With a Mentor Commented:
This wold be best handled with a UDF, however this also work:

Select      Count(*),
                SUBSTRING(YourColumnName, CHARINDEX(',', YourColumnName) + 1, CHARINDEX(',', YourColumnName, CHARINDEX(',', YourColumnName) + 1) - CHARINDEX(',', YourColumnName) - 1) 'SHIPMENT METHOD'
From YourTableName
Group By SUBSTRING(YourColumnName, CHARINDEX(',', YourColumnName) + 1, CHARINDEX(',', YourColumnName, CHARINDEX(',', YourColumnName) + 1) - CHARINDEX(',', YourColumnName) - 1)
0
 
Anthony PerkinsCommented:
>>I am looking for a simple select statement <<
So using a User Defined Function is out of the question?
0
 
appariCommented:
try something like this

select count(*), substring(columnname, charindex(',',columnname)+1, charindex(',',columnname, charindex(',',columnname) +1 )-1 ) from tableName
group by substring(columnname, charindex(',',columnname)+1, charindex(',',columnname, charindex(',',columnname) +1 )-1 )

0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Anthony PerkinsCommented:
appari

I get this:
1      FedEx by Friday Afternoon,12,0
1      FedEx by Friday Afternoon,19,0
1      FedEx by Thursday Afternoon,12,0
2      Standard US Mail,6.5,0
1      US Priority Mail w/Conf ,7.25,0
1      USPS Airmail Parcel Post ,33.5
0
 
bbramanAuthor Commented:
Running against the full data set your statement returned the following error:

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

_____________________________________________________

Would the user defined function your are saying would be best allow me to simply select the 'shipment method' as if it were it's own field?   I have never worked with User Defined Function in SQL Server before.
0
 
bbramanAuthor Commented:
05/05/2005 07:40PM PDT is in response to acperkins 05/05/2005 07:31PM PDT comment.
0
 
Anthony PerkinsCommented:
>>Running against the full data set your statement returned the following error:<<
That means that one of the values does not contain two commas, in other words it is not valid. Your best bet is to fix it.

>>Would the user defined function your are saying would be best allow me to simply select the 'shipment method' as if it were it's own field?<<
Nope.  But it would give you better control over the exceptions.  There is no getting a way from the fact that you have to write some code to parse for the values and handle exceptions.
0
 
Anthony PerkinsCommented:
In order to find the culprit use something like this:

Select *
From TableName
Where LEN(YourColumnName) - LEN(REPLACE(YourColumnName, ',', '')) <> 2
0
 
Anthony PerkinsCommented:
That last query should list all the rows that do not contain 2 commas.

Strictly speaking the only ones that should cause problems are the ones that have less than 2, so this should be better:
Select *
From TableName
Where LEN(YourColumnName) - LEN(REPLACE(YourColumnName, ',', '')) < 2
0
 
bbramanAuthor Commented:
acperkins,

Your query revealed that this application allows this field to contain simply 'No shipping required for this order.'

Could a 'User Defined Function' be written to allow me to select the 'shipment method' as if it were it's own field, while excluding records containing the text ' No shipping required for this order. '?
0
 
Anthony PerkinsCommented:
>>Could a 'User Defined Function' be written to allow me to select the 'shipment method' as if it were it's own field, while excluding records containing the text ' No shipping required for this order. '?<<
No. As I mentioned before it would make the query easier to read.

Here is how you can do it without a UDF:

Select     Count(*),
                SUBSTRING(YourColumnName, CHARINDEX(',', YourColumnName) + 1, CHARINDEX(',', YourColumnName, CHARINDEX(',', YourColumnName) + 1) - CHARINDEX(',', YourColumnName) - 1) 'SHIPMENT METHOD'
From YourTableName
Group By SUBSTRING(YourColumnName, CHARINDEX(',', YourColumnName) + 1, CHARINDEX(',', YourColumnName, CHARINDEX(',', YourColumnName) + 1) - CHARINDEX(',', YourColumnName) - 1)
Where YourColumnName <> 'No shipping required for this order'
0
 
Anthony PerkinsCommented:
I place the Where after the GROUP BY is should be before. I also may have missed the final period.  So make sure it matches exactly. If there is more than one than use:

Select     Count(*),
                SUBSTRING(YourColumnName, CHARINDEX(',', YourColumnName) + 1, CHARINDEX(',', YourColumnName, CHARINDEX(',', YourColumnName) + 1) - CHARINDEX(',', YourColumnName) - 1) 'SHIPMENT METHOD'
From YourTableName
Where YourColumnName Is Not in ('No shipping required for this order.', 'Another row you want to exclude', 'And another row you want to exclude')
Group By SUBSTRING(YourColumnName, CHARINDEX(',', YourColumnName) + 1, CHARINDEX(',', YourColumnName, CHARINDEX(',', YourColumnName) + 1) - CHARINDEX(',', YourColumnName) - 1)

Or you could just simply write:

Select     Count(*),
                SUBSTRING(YourColumnName, CHARINDEX(',', YourColumnName) + 1, CHARINDEX(',', YourColumnName, CHARINDEX(',', YourColumnName) + 1) - CHARINDEX(',', YourColumnName) - 1) 'SHIPMENT METHOD'
From YourTableName
Where LEN(YourColumnName) - LEN(REPLACE(YourColumnName, ',', '')) < 2
Group By SUBSTRING(YourColumnName, CHARINDEX(',', YourColumnName) + 1, CHARINDEX(',', YourColumnName, CHARINDEX(',', YourColumnName) + 1) - CHARINDEX(',', YourColumnName) - 1)
0
 
bbramanAuthor Commented:
Thank You for your help acperkins !!!  A+

Note: To get it to work I did need to move the where clause before the 'group by' as follows:

Select     Count(*),
                SUBSTRING(shipmentdetails, CHARINDEX(',', shipmentdetails) + 1, CHARINDEX(',', shipmentdetails, CHARINDEX(',', shipmentdetails) + 1) - CHARINDEX(',', shipmentdetails) - 1) 'SHIPMENT METHOD'
From orders
Where shipmentdetails <> 'No shipping required for this order.'
Group By SUBSTRING(shipmentdetails, CHARINDEX(',', shipmentdetails) + 1, CHARINDEX(',', shipmentdetails, CHARINDEX(',', shipmentdetails) + 1) - CHARINDEX(',', shipmentdetails) - 1)
0
 
Anthony PerkinsCommented:
This:
>>I place the Where after the GROUP BY is should be before. <<
Should read:
I placed the Where clause after the GROUP BY when it should be before.
0
 
bbramanAuthor Commented:
Great, I think we posted in the same minute.  Thanks again for your help !!!
0
 
bbramanAuthor Commented:
Regarding user defined functions, are you saying it is not possible using a UDF to exclude part of the data and for that reason a UDF would never work with my data?
0
 
Anthony PerkinsCommented:
>>are you saying it is not possible using a UDF to exclude part of the data and for that reason a UDF would never work with my data?<<
No.  UDF functions just let you encapsulate some difficult code so that when you use it instead of doing having to repeatedly do the above, you could do something like this:

Select [COUNT],
          [SHIPMENT METHOD]
From  dbo.udf_GetAllShippingMethods

As you can see that is a lot easier on the eyes!
0
 
Anthony PerkinsCommented:
Here is the UDF from my previous comment:

CREATE FUNCTION dbo.udf_GetAllShippingMethods ()

RETURNS @Temp Table (
            [COUNT] integer,
            [SHIPMENT METHOD] varchar(100))
AS  
BEGIN
Insert      @Temp ([COUNT], [SHIPMENT METHOD])
Select    Count(*),
              SUBSTRING(YourColumnName, CHARINDEX(',', YourColumnName) + 1, CHARINDEX(',', YourColumnName, CHARINDEX(',', YourColumnName) + 1) - CHARINDEX(',', YourColumnName) - 1)
From      YourTableName
Where      YourColumnName <> 'No shipping required for this order.'
Group By
      SUBSTRING(YourColumnName, CHARINDEX(',', YourColumnName) + 1, CHARINDEX(',', YourColumnName, CHARINDEX(',', YourColumnName) + 1) - CHARINDEX(',', YourColumnName) - 1)
Return
END

As you can see you still need the code, it just hides the complexity. Especially if you are calling this function from many different places.  You now only have to fix it in one place.

This can be handled other ways, simplifying the above code substantially, but you should get the idea.
0
All Courses

From novice to tech pro — start learning today.