SQL Puzzle | Count different type of characters from a string Puzzle

This question was asked to me in one of the technical interviews I had attended. Here you have an input string and you have count uppercase characters , lowercase characters and remaining characters from a string.

Please check out the sample input values and sample expected output below.

Sample Input

DECLARE @ AS VARCHAR(1000) = ‘AddsfsdfWUES 12*&’

Expected Output

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 Numbers Table and ASCII Function

--



DECLARE @ AS VARCHAR(1000) = 'AddsfsdfWUES 12*&'

SELECT  
	    SUM(CASE WHEN ASCII(chr) BETWEEN 65 AND 90 THEN 1 ELSE 0 END) UpperCaseCount
	   ,SUM(CASE WHEN ASCII(chr) BETWEEN 97 AND 122 THEN 1 ELSE 0 END) LowerCaseCount
	   ,SUM(CASE WHEN (( ASCII(chr) NOT BETWEEN 97 AND 122 ) AND ( ASCII(chr) NOT BETWEEN 65 AND 90 )) THEN 1 ELSE 0 END) OtherChrCount	
FROM 
(
	SELECT DISTINCT Number , SUBSTRING(@,Number,1) chr
	FROM Master..spt_Values 
	WHERE Number > 0 and number <= LEN(@)
)p	


--

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

SQL Puzzle | The Multiple Sums Puzzle

This question was asked to me in one of the technical interviews I had recently attended. Here you have a column with values 1 to 7. Now you have to find sum of each digit with each remaming digits and get resuts where you have same sum multiple times.

Let me give you an example.

1+6 = 7
6+1 = 7
3+4 = 7
4+3 = 7
5+2 = 7
2+5 = 7

Now from the above example we need 3 rows only. e.g. 1+6 or 6+1 is same for an user.

Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

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 CrossJoin & Concat

--



IF OBJECT_ID('tempdb..#One') IS NOT NULL
    DROP TABLE #One

IF OBJECT_ID('tempdb..#Two') IS NOT NULL
    DROP TABLE #Two

SELECT s.ID a , x.ID b , s.ID + x.ID Sums INTO #One FROM SumPuzzle s
CROSS APPLY
(
	SELECT * FROM  SumPuzzle s1
)x 

SELECT Sums INTO #Two FROM #One
GROUP BY Sums
HAVING COUNT(*) > 1

SELECT LEFT(Rnk,1) ID , RIGHT(Rnk,1) ID , Sums
FROM
(
	SELECT DISTINCT b.Rnk, b.Sums FROM
	(
		SELECT c2.* , CASE WHEN a > b THEN CONCAT(a,b) ELSE CONCAT(b,a) END Rnk FROM #Two c1
		INNER JOIN #One c2 
		ON c1.Sums = c2.Sums		
	)b 
)v

IF OBJECT_ID('tempdb..#One') IS NOT NULL
    DROP TABLE #One

IF OBJECT_ID('tempdb..#One') IS NOT NULL
    DROP TABLE #Two



--

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