# Count multiple unique characters from a string

Hello,

I'm trying to count the number of times certain characters appear in a string.
My data can look like:

AA
AAB1
AAB1B2

The AA will never have a number designation next to it but B's will range from 0-17 and I need to count them based on B0-B1, B2-B17.
To count the number of A's I'm using:
SUM((LEN(column)-LEN(REPLACE(column,'A','')))/LEN('A')) as A
Is there a way that I can count multiple criteria without having to enter each one individually and adding them together?

Thank you.

I think the query you are trying is correct, please make sure there is no extra space or quotation marks

If you'd have normalised data, like

``````id   fk   kars
1    1    AA
2    1    B1
3    1    B2
``````

Counting the number of distinct kars per fk would be so easy.

``````SELECT fk
, SUM(CASE WHEN kar = 'AA' THEN 1 ELSE 0 END) as AA
, SUM(CASE WHEN kar IN ( 'B0', 'B1')  THEN 1 ELSE 0 END) as B0_B1
, SUM(CASE WHEN kar NOT IN ( 'AA', 'B0', 'B1')  THEN 1 ELSE 0 END) as B2_B17
FROM normalised_table
GROUP BY fk
``````

SQL has a hard time digesting denormalised data like you have.

I would suggest to proceed in two steps:

1. normalise your data and put it in a table. By putting the result in a table makes debugging your code way easier.
2. from there on, work with the normalised data in the table using SQL
1 Like

hi

are you still interested in finding a solution for this ???

I will try Thank you for your help, I'm good to go.

i will put my solution ..

BUT i need to practice this is what i am doing

use tempdb
go

drop table #data
go

create table #data
(
data varchar(100)
)
go

insert into #data select 'AA'
insert into #data select 'AAB1'
insert into #data select 'AAB1B2'
go

select * from #data
go

drop FUNCTION [dbo].[SplitString]
go

CREATE FUNCTION [dbo].[SplitString]
(
@List NVARCHAR(MAX),
@Delim VARCHAR(255)
)
RETURNS TABLE
AS
RETURN ( SELECT [Value] FROM
(
SELECT
[Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_objects) AS x
WHERE Number <= LEN(@List)
AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
) AS y
);
go

SELECT *
FROM ( select data from #data
) AS A
CROSS APPLY dbo.[SplitString] (A.data,'[]') AS abc

I found a solution

Create Function
``````CREATE FUNCTION [dbo].[Split](@String varchar(8000))
returns @temptable TABLE (items varchar(8000))
as
begin

declare @idx int   =1
declare @slice varchar(1) = ''
declare @len int = 0
select @len = LEN(@string)

while @idx <= @len
begin

set @slice = SUBSTRING(@string,@idx,1)
insert into @temptable(Items) values(@slice)

set @idx = @idx  +1

end
return
end
``````
Create Data Script
``````use tempdb
go

drop table #data
go

create table #data
(
data varchar(100)
)
go

insert into #data select 'AA'
insert into #data select 'AAB1'
insert into #data select 'AAB1B2'
go
``````
SQL
``````SELECT abc.items AS character,
Count(*)  AS number_of_occurences
FROM   (SELECT data
FROM   #data) AS A
CROSS apply dbo.[Split] (A.data) AS abc
GROUP  BY abc.items
``````
Result

OR If you want result by each STRING

New SQL
``````SELECT a.data,
abc.items        AS character,
Count(abc.items) AS count_characters
FROM   (SELECT data
FROM   #data) AS A
CROSS apply dbo.[Split] (A.data) AS abc
GROUP  BY a.data,
abc.items
ORDER  BY 1
``````
New Result

hi

I know you are good to go
YOUR wish I have another way of doing this
using the help of tally tables ..

Data Script
``````use tempdb
go

drop table #data
go

create table #data
(
data varchar(100)
)
go

insert into #data select 'AA'
insert into #data select 'AAB1'
insert into #data select 'AAB1B2'
go

select * from #data
go
``````
SQL
``````;WITH tally_cte
AS (SELECT N=1
UNION ALL
SELECT n + 1
FROM   tally_cte
WHERE  n <= 20),
def_cte
AS (SELECT data,
Substring(data, n, 1) AS ch
FROM   tally_cte,
#data
WHERE  Substring(data, n, 1) <> ''
GROUP  BY data,
n)
SELECT data,
ch,
Count(ch)
FROM   def_cte
GROUP  BY data,
ch
``````
Result