0

Alright, so basically I just need to figure out how many of each number there are in a line. So for example:

3 2 9 4 3 3 4 3 4 3 3 4 4 3 3 3 3 3 3 3 3 3 3 3 3 2 3 3 3 3 3 3 4 3 3 4 13 13 4 3 3 13 3 13 13 13 13 13 13 13 13 13 9 4 4 4 4 3 5 3 9 10 3 4 8 10 4 3 4 13 13 13 13 4 2 5 5 13 13 13 13 13 4 13 13 13 13 13 13 13 13 13 13 13 13 13 13 13 9 13

These are the numbers, all in one line. I need to count how many 2's, 3's, 4's, etc. there are in this line. How would I go about doing this?

The functions are using SQL, more specifically I'm using the Klipfolio service.

Tyler Barney
  • 1

1 Answers1

0

The basic idea is to split the string (delimited by comma) and count/aggregate the rows from the split result (table). Also see T-SQL split string for more efficient ways to split a string.

Example:

DECLARE @String varchar(100) = '3 2 9 4 3 3 4 3 4 3 3 4 4 3 3 3 3 3 3 3 3 3 3 3 3 2 3 3 3 3 3 3 4 3 3 4 13 13 4 3 3 13 3 13'
-- To change ',' to any other delimeter, just change ',' to your desired one
DECLARE @Delimiter CHAR = ' '    

SELECT Num, Count(Num) ct
FROM (
    SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Num' 
    FROM  
    (     
         SELECT CAST ('<M>' + REPLACE(@String, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data            
    ) AS A 
    CROSS APPLY Data.nodes ('/M') AS Split(a)
) A GROUP BY Num

Produces:

Num   ct
---   ---
 13    4
  2    2
  3   29
  4    8
  9    1
Community
  • 1
  • 1
tgolisch
  • 6,549
  • 3
  • 24
  • 42