In this post, we will see how we can find out what are the different characters available in a string and what is the number of occurrence of each individual character in the string. The first solution that comes to our mind is to use a while loop. This is very trivial solution and most of the beginners will code this way.
CREATE FUNCTION dbo.AllCharacterOccurence (@str varchar(MAX))
RETURNS @tbl TABLE (chr char, freq int)
AS
BEGIN
declare @lp as int
declare @tbl2 table (chr char)
set @lp = 1
while (@lp <= LEN(@str))
begin
insert @tbl2
select SUBSTRING(@str,@lp,1)
set @lp = @lp + 1
end;
insert @tbl
select chr alpha, COUNT(*) freq
from @tbl2
group by chr
return;
END;
GO
Here we see a table valued function that takes a string as the parameter and finds the number of occurrence of each individual character in the string.
Now we will see a different solution to this problem which is not very trivial, but seems to be very interesting. We want to get the result but dont want to use any loops. We will use pure sql to do this.
CREATE FUNCTION dbo.AllCharacterOccurence (@str varchar(MAX))
RETURNS @Characters TABLE (chr char, freq int)
AS
BEGIN
insert into @Characters
select chr, COUNT(*)
from
(
select substring(@str, sequence, 1) as chr
from dbo.IntegerSequence(1, LEN(@str))
) A
group by chr
return;
END;
GO
SELECT * from dbo.AllCharacterOccurence ('This should be very easy to do');
The output will be like this. Just the same as the previous function.
Now let's see how this works.
In this function we have used another function dbo.IntegerSequence(1, LEN(@str)) that gives us the integer numbers starting from 1 to the length of the string. The Integer Sequence generator function can be written as follows.
CREATE FUNCTION dbo.IntegerSequence(@start_number int,@end_number int )
RETURNS @SequenceTable TABLE (sequence int)
AS
BEGIN
;With IntegerSequence( Number ) as -- Common table expression
(
Select @start_number as Number
union all
Select Number + 1
from IntegerSequence
where Number < @end_number
)
insert into @SequenceTable
Select N.number
From IntegerSequence N
Option (MaxRecursion 0)
return;
END;
GO
So we take the numbers in different rows. (Figure - 1)
(Figure - 1) (Figure - 2) (Figure - 3)
Now we can use the string in another column together with this select. So we will get the same string in every row. (Figure - 2)
SELECT sequence, @str
from dbo.IntegerSequence (1, 30)
Now we can easily use substring method using the string and the index position to find out the character at the specified position. (Figure - 3)
SELECT sequence, @str, substring(@str, sequence, 1)
from dbo.IntegerSequence (1, 30)
The rest is really easy. You can just use the counting grouping by the characters.