Tags

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


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

Advertisements