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

Expected Output

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