How to count a character in SQL Server?

Question

Please help me - I have problem with counting how many 'a' (character) there are in a row and column.

This is my query :

declare @zz as varchar(10) = '123a123a12'
select @zz

What function in SQL Server 2008 R2 can I use to count how many 'a' are in there?

How can I combine charindex with len?

Thanks

Please, refer updated answer. it helps you to find out char from a sting. — Vikram Jain, Mar 20 '19 at 09:45

score 2 · Answer 1 · answered Mar 20 '19 at 07:35

The old school trick here is to replace the 'a's with empty spaces ('') and compare the resulting string's length to the length of the original:

declare @zz as varchar(10) = '123a123a12' 

declare @zz_without_a varchar(10)=replace(@zz,'a','')

declare @a_in_zz int=len(@zz)-len(zz_without_a)

Zhorov · Answer 2 · 2019-03-20T07:53:51.737

One possibe approach is to use REPLACE() and LEN() functions.:

DECLARE @zz varchar(10) = '123a123a12' 
SELECT LEN(@zz) - LEN(REPLACE(@zz, 'a', '')) AS CharCount

Output:

CharCount
        2

Another possible approach, if you want to count more than one character, is to use recursion:

DECLARE @zz varchar(10) = 'aa3a123a12' 
;WITH cte AS (
   SELECT 1 AS N
   UNION ALL
   SELECT N + 1
   FROM cte
   WHERE N < LEN(@zz)
)
SELECT COUNT(*) AS CharCount
FROM cte
WHERE SUBSTRING(@zz, N, 1) IN ('a', '1')

score 0 · Answer 3 · answered Mar 20 '19 at 07:42

I would use REPLACE and DATALENGTH instead of LEN, because of trailing spaces that could appear after replace. Example:

DECLARE @zz as varchar(20) = '123a123a12      a' 
SELECT DATALENGTH(@zz) - DATALENGTH(REPLACE(@zz, 'a', '')),
    LEN(@zz) - LEN(REPLACE(@zz, 'a', ''))

The output is 3 and 9. If @zz is NVARCHAR, not VARCHAR, you will have to divide by 2.

From MSDN:

DATALENGTH function returns the number of bytes used to represent any expression

How to count a character in SQL Server?

3 Answers3