SQL Puzzle | The Weird String Puzzle

In this puzzle you have to read a string value for example – 11Ab220111bbb5555ccc4444.
We have to get data like below-

If any character is repeating after the first occurance we have to print that number.
Example
1 -> for this we need to print 111 since 1 is repeating.
b -> for tihs we need to print bbb since b is repeating.
for other just print the character as they are not repeating. We do not have to print the duplicate characters.

Sample Input

WeirdValue
11Ab220111bbb5555ccc4444

Expected Output

Number PrintAs
0 0
1 1, 1, 1
2 2
4 4
5 5
A A
b b, b, b
c c

Script

Use below script to create table and insert sample data into it.

 -- DECLARE @WeirdValue NVARCHAR(255) = N'11Ab220111bbb5555ccc4444'; SELECT @WeirdValue WeirdValue --

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

 -- DECLARE @WeirdValue NVARCHAR(255) = N'11Ab220111bbb5555ccc4444'; ;WITH SingleDigits(Number) AS ( SELECT Number FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (0)) AS X(Number) ) ,Series AS ( SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number from SingleDigits as d1, SingleDigits as d2, SingleDigits as d3, SingleDigits as d4 ) ,CTE AS ( SELECT *, SUBSTRING(@WeirdValue,Number,1) Nums FROM Series WHERE Number <= LEN(@WeirdValue) ) ,CTE1 AS ( SELECT * , CASE WHEN Nums = lag(Nums) OVER(ORDER BY Number) THEN 0 ELSE 1 END cols FROM CTE ) ,CTE2 AS ( SELECT * , SUM(cols) OVER (PARTITION BY Nums ORDER BY Number) grouper FROM CTE1 ) ,CTE3 AS ( SELECT nums FROM CTE2 c WHERE grouper = 2 UNION ALL SELECT DISTINCT nums FROM CTE2 r WHERE grouper = 1 AND NOT EXISTS ( SELECT NULL FROM CTE2 c1 WHERE r.Nums = c1.Nums AND grouper = 2 ) ) ,CTE4 AS ( SELECT DISTINCT nums nums1 , STUFF (( SELECT ', ' + CAST(nums AS VARCHAR) FROM CTE3 a WHERE ( a.nums = b.nums ) FOR XML PATH('') ) ,1,2,'') AS nums FROM CTE3 b ) SELECT nums1 Number , nums PrintAs FROM CTE4 --

Output

--

/*------------------------
DECLARE @WeirdValue NVARCHAR(255) = N'11Ab220111bbb5555ccc4444';

;WITH SingleDigits(Number) AS
(
SELECT Number
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
(9), (0)) AS X(Number)
)
,Series AS
(
SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
from
SingleDigits as d1,
SingleDigits as d2,
SingleDigits as d3,
SingleDigits as d4
)
,CTE AS
(
SELECT *, SUBSTRING(@WeirdValue,Number,1) Nums FROM Series
WHERE Number <= LEN(@WeirdValue)
)
,CTE1 AS
(
SELECT * ,  CASE WHEN Nums = lag(Nums)
OVER(ORDER BY Number) THEN 0 ELSE 1 END cols
FROM
CTE
)
,CTE2 AS
(
SELECT * , SUM(cols) OVER (PARTITION BY Nums ORDER BY Number) grouper FROM CTE1
)
,CTE3 AS
(
SELECT nums FROM CTE2 c WHERE grouper = 2
UNION ALL
SELECT DISTINCT nums FROM CTE2 r WHERE grouper = 1
AND NOT EXISTS ( SELECT NULL FROM CTE2 c1 WHERE r.Nums = c1.Nums AND grouper = 2 )
)
,CTE4 AS
(
SELECT DISTINCT nums nums1 , STUFF
((
SELECT ', ' + CAST(nums AS VARCHAR)
FROM CTE3 a
WHERE ( a.nums = b.nums )
FOR XML PATH('')
) ,1,2,'')
AS nums
FROM CTE3 b
)
SELECT nums1 Number , nums PrintAs FROM CTE4
------------------------*/
Number PrintAs
------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0      0
1      1, 1, 1
2      2
4      4
5      5
A      A
b      b, b, b
c      c

(8 row(s) affected)

--

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