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:
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 ..
your wish ..
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 [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
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 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
OR If you want result by each STRING
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
hi
I know you are good to go
YOUR wish 
I have another way of doing this
using the help of tally tables ..
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
;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
