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

Advertisements