Tags

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


SQL Puzzle | Remove Duplicate Ints From String Puzzle

Write a T-SQL to remove the duplicate int values present in the string column. You also have to remove the Single Int character present in the string.

For more please check out the sample input and the expected output.

Sample Inputs

PawanName Pawan_slug_name
1 PawanA-111
2 PawanB-123
3 PawanB-32
4 PawanC-4444
5 PawanD-3

Expected Output

PawanName Pawan_slug_name
1 PawanA
2 PawanB-123
3 PawanB-32
4 PawanC
5 PawanD

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--

CREATE TABLE RemoveDuplicateIntsFromNames
(
	  PawanName INT
	, Pawan_slug_name VARCHAR(1000)
)
GO


INSERT INTO RemoveDuplicateIntsFromNames VALUES
(1,  'PawanA-111'  ),
(2, 'PawanB-123'   ),
(3, 'PawanB-32'    ),
(4, 'PawanC-4444' ),
(5, 'PawanD-3'  )
GO


--

—

SOLUTION – 1


--


;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) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3   
)
SELECT DISTINCT PawanName , CASE WHEN cnt = dcnt AND cnt >= 1 THEN chrs else Pawan_slug_name END Pawan_slug_name  FROM 
(
	SELECT * , COUNT(SUBSTRING(Ints,Number,1)) OVER (PARTITION BY PawanName) cnt , COUNT(SUBSTRING(Ints,Number,1)) OVER (PARTITION BY PawanName,SUBSTRING(Ints,Number,1)) dcnt FROM 
	(
		SELECT * , SUBSTRING(Pawan_slug_name,0,CHARINDEX('-',Pawan_slug_name,0)) chrs
		, SUBSTRING(Pawan_slug_name,CHARINDEX('-',Pawan_slug_name,0)+1,DATALENGTH(Pawan_slug_name)) Ints  FROM RemoveDuplicateIntsFromNames
	)k
	CROSS APPLY
	( 
		SELECT DISTINCT number FROM
		Series WHERE number > 0 AND number <= DATALENGTH(k.Ints) 
	)x
)m
ORDER BY PawanName 

--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com