SQLTeam.com | Weblogs | Forums

Count multiple unique characters from a string


#1

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.


#2

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


#3

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

#4

hi

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

I will try :slight_smile:


#5

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


#6

i will put my solution ..

your wish ..

BUT i need to practice :wink:

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


#7

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


#8

hi

I know you are good to go
YOUR wish :slight_smile:

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