SQL Puzzle | The ASCII Puzzle – II

This is variation of the puzzle (https://msbiskills.com/2015/04/25/t-sql-query-the-ascii-puzzle/).

In the puzzle we have an input string and we have to find ASCII value for each character present in the string and position of each character in the string.

Please check out the sample input and the expected output below-

Sample Input

DECLARE @ VARCHAR(1000)= ‘KJhdsfmdsnfsd sdkfjsdk fjsd ‘

Expected Output

Position Chr AsciiChr
1 K 75
2 J 74
3 h 104
4 d 100
5 s 115
6 f 102
7 m 109
8 d 100
9 s 115
10 n 110
11 f 102
12 s 115
13 d 100
14 32
15 s 115
16 d 100
17 k 107
18 f 102
19 j 106
20 s 115
21 d 100
22 k 107
23 32
24 f 102
25 j 106
26 s 115
27 d 100
28 32

Solution 1 | Using Numbers Table

 ``` -- DECLARE @ VARCHAR(1000)= 'KJhdsfmdsnfsd sdkfjsdk fjsd ' SELECT Number Position , SUBSTRING(@,Number,1) Chr, ASCII(SUBSTRING (@,Number,1)) AsciiChr FROM ( SELECT DISTINCT Number FROM Master..Spt_Values WHERE Number > 0 AND Number <= DATALENGTH(@) )r -- ```

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