tags:

views:

499

answers:

2

For a user logging table I have in a SQL database, I track the some of the parameters off of a report request. The report allows multiple ID's to be passed to it and I store all of those in a single column in the database column. If this were to be a normalized set of data, there would definitely be an additional table setup for this, but this is what was inherited...

I've now been asked to give a quick count of the number of times a report was run with more than 2 ID's passed to it. I can easily get the number of records that have more than 1 report requested because they all include a comma.

What I need to do next is count the number of times a comma appears in a column. How do you do this in SQL?

--count the number of times more than 1 report was requested in the record
select 
    count(*) as cnt
from
    [table]
where
    RequestedReportParams Like '%,%'
+5  A: 
SELECT LEN(RequestedReportParams) - LEN(REPLACE(RequestedReportParams, ',', ''))
FROM YourTable
WHERE .....

This is simply comparing the length of the column with the commas, with the length of the value with the commas removed, to give you the difference (i.e. the number of commas)

AdaTheDev
I've focussed on answering your question in bold, rather than perhaps what you actually may need to solve your actual goal. In which case mherren's answer may be more suitable.
AdaTheDev
Added `LEN` around the `REPLACE`, since `REPLACE` returns a string, not an int, which means your math wouldn't work. +1 for the creative solve, though!
Eric
Oops @ my forgetting to put LEN() in! - Thanks Eric!
AdaTheDev
This is just what I needed. Thank you so much for the quick response.
RSolberg
If using this to write a function or to count any given character you'll prob want to use DATALENGTH(...) instead of LEN() since select len(' ') = 0, not 1.
Rory
+3  A: 

It seems the quick and dirty way to answer the question you've been asked would be to do this:

select 
    count(*) as cnt
FROM 
    [table]
WHERE 
    RequestedReportParams Like '%,%,%'
That seems so hackerish, why didn't I think of it! Only issue is that I'll have to keep adding an additional %, for each number they request information on...
RSolberg
+1 for actually solving the goal, not the boldened question!
AdaTheDev
This did solve the goal of what I had been asked right now. Complete agree with @AdaTheDev, but the @AdaTheDev's idea took it a step further an I can now point out a distribution of the quantities, etc. Thanks so much for the quick response. Much appreciated. Also, welcome to SO!
RSolberg