SQL Puzzle | The Facebook Puzzle # 2 ( Count Characters in a string Puzzle )

Tags

SQL Puzzle | The Facebook Puzzle # 2

Puzzle Statement

This puzzle was asked in social media ( Facebook ), Here you have a string and you have to count how many times each character appear in the string. Please check sample input and expected output.

Sample Input String

‘aaaaaabbbbbccccc’

Expected Output

Charx	CharxCounts
a	6
b	5
c	5

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using Simple SELECT & Numbers Table

--

DECLARE @str AS VARCHAR(100) = 'aaaaaabbbbbccccc'

SELECT SUBSTRING(@str,number,1) Charx , COUNT(SUBSTRING(@str,number,1)) CharxCounts
FROM 
( 
	SELECT DISTINCT number FROM
	MASTER..SPT_VALUES WHERE number > 0 AND number <= DATALENGTH(@str)
)v
GROUP BY SUBSTRING(@str,number,1)

--

In the above solution I am using numbers table. Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

5 thoughts on “SQL Puzzle | The Facebook Puzzle # 2 ( Count Characters in a string Puzzle )”

Marcus Hamilton said:

December 28, 2015 at 6:20 AM

Here’s an alternative way of doing it:

declare @LettersTable table(letters varchar(max));
insert into @LettersTable select ‘aaaaaabbbbbccccc’;

with cte
as
(
select letters, substring(letters, 1, 1) letter, 1 [index]
from @LettersTable

union all

select letters, substring(letters, [index] + 1, 1) letter, [index] + 1 [index]
from cte
where [index] + 1 <= len(letters)
)
select letter, count(letter)
from cte
group by letter;

Liked your solution, but changing it slightly makes it more performant:

SELECT SUBSTRING(@str,number,1) Charx , COUNT(SUBSTRING(@str,number,1)) CharxCounts
FROM
(
select row_number() over(order by (select null)) number
from sys.objects
order by object_id asc
offset 0 rows fetch next len(@str) rows only
)v
GROUP BY SUBSTRING(@str,number,1);

LikeLike

Pawan Kumar Khowal said:

December 30, 2015 at 11:19 AM

Excellent Sir!!

LikeLike

Yulia Igusheva said:

January 26, 2016 at 6:15 AM

Nice question. Thanks.

LikeLike

Sappho03 (@sappho0101) said:

March 26, 2018 at 5:12 PM

DECLARE @str AS VARCHAR(100) = ‘aaaaaabbbbbccccc’

DECLARE @t1 table (Charx char(1))
DECLARE @start int=1

WHILE(LEN(@str)>0)
BEGIN

INSERT INTO @t1
SELECT SUBSTRING(@str,@start,1)

SET @str=SUBSTRING(@str,@start+1,LEN(@str))

END

SELECT
Charx,
COUNT(*) CharxCounts
FROM @t1
GROUP BY Charx

LikeLike

Gregor Kralj said:

May 16, 2019 at 11:39 AM

declare @input varchar(100) = ‘aaaaaabbbbbccccc’;
with cte as (
select SUBSTRING(@input, 1, 1) as onechar, 1 as position
union all
select SUBSTRING(@input, position + 1, 1) as onechar, position + 1 as position
from cte
where position < LEN(@input)
)
select onechar, COUNT(*) as CharCount
from cte
group by onechar
order by COUNT(*) desc

LikeLiked by 1 person

Improving my SQL BI Skills

SQL Puzzle | The Facebook Puzzle # 2 ( Count Characters in a string Puzzle )

5 thoughts on “SQL Puzzle | The Facebook Puzzle # 2 ( Count Characters in a string Puzzle )”

Leave a comment Cancel reply

Share this

Related

5 thoughts on “SQL Puzzle | The Facebook Puzzle # 2 ( Count Characters in a string Puzzle )”

Leave a comment Cancel reply