Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

Advertisements