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

Expected Output

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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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.

Enjoy !!! Keep Learning

Http://MsbiSkills.com