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

UpperCaseCount LowerCaseCount OtherChrCount
5 7 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 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