CODEWITHSUNDEEP
sqlsql-servercountcharcharacter
Daniel
Daniel

Reputation: 121

Record counts where column contains char(x)

I need to count the number of records where a column (varchar(x)) contains a particular character, for all ASCII characters 1-255. This is what I have so far (in a proc)...

DECLARE @CHAR TINYINT = 1
CREATE TABLE #CHARCOUNTS  (CHAR TINYINT, COUNT INT)

WHILE @CHAR < 255

BEGIN
    INSERT INTO #CHARCOUNTS(CHAR, COUNT) VALUES (@CHAR,(SELECT COUNT (ID) FROM MyTable WHERE CHARINDEX(CHAR(@CHAR), MyColumn) > 0))
    SET @CHAR = @CHAR + 1
END 

SELECT CHAR, COUNT FROM #CHARCOUNTS

This returns a result set telling me there are X records containing CHAR(1), X records containing CHAR(2) and so on, however the query is taking several minutes to complete on approx 5 million records as I guess it has to traverse every record 255 times. Any suggestions for a more efficient/faster way of doing this?

Many thanks.

Upvotes: 3

Views: 117

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

Try this SET BASED APPROACH.

Use a Recursive CTE to generate the numbers.

Then use cross apply to find the count of each char in your column. In a single shot you can find all the char count and in a 5 millions record also single insert instead of 255 inserts, so this will have a better performance. 

;WITH cte
     AS (SELECT 1 chars
         UNION ALL
         SELECT chars + 1
         FROM   cte
         WHERE  chars < 255)
INSERT INTO #CHARCOUNTS
            (CHAR,COUNT)
SELECT chars,
       [count]
FROM   cte
       CROSS apply (SELECT Count (ID) [count]
                    FROM   MyTable
                    WHERE  Charindex(Char(chars), MyColumn) > 0) cs
OPTION (maxrecursion 256)

Note: There are so many ways to generate number's since the count here is very less i have chosen Recursive CTE over the other methods. Check here for more info

Upvotes: 2

Related Questions