SQL Puzzle | Remove Consecutive Integers Puzzle

In this puzzle you have to remove consecutive integers(including hyphen) from the Dat column. For more details please check the sample input and expected output.

Sample Input

Id Dat
1 INDIA IT 19705-23318 IND
2 INDIA IT 98011 IND
3 INDIA IT IND
4 NULL

Expected Output

Id Dat NewDat
1 INDIA IT 19705-23318 IND INDIA IT IND
2 INDIA IT 98011 IND INDIA IT IND
3 INDIA IT IND INDIA IT IND
4 NULL NULL

Script

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

 ```-- CREATE TABLE RemoveIntegers ( Id INT ,Dat VARCHAR(250) ) GO INSERT INTO RemoveIntegers VALUES ('INDIA IT 19705-23318 IND'), ('INDIA IT 98011 IND'), ('INDIA IT IND'), (NULL) GO SELECT * FROM RemoveIntegers GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1

 ```-- ;WITH CTE AS ( SELECT *, PATINDEX ( '%[0-9]%' , Dat ) sta , PATINDEX ( '%[a-zA-Z]%' , SUBSTRING (Dat , PATINDEX ( '%[0-9]%' , Dat ) , 1000 )) chars FROM RemoveIntegers ) SELECT Id, Dat , CASE WHEN sta = 0 THEN Dat else STUFF(Dat,sta,chars-1,'') end NewDat FROM CTE -- ```

Output-1

 ```-- Id Dat NewDat ----------- ------------------------------ --------------------- 1 INDIA IT 19705-23318 IND INDIA IT IND 2 INDIA IT 98011 IND INDIA IT IND 3 INDIA IT IND INDIA IT IND 4 NULL NULL (4 rows affected) -- ```

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

Author Introduction: Pawan Khowal

Pawan is a SQL Server Expert. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL