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.

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