SQL Puzzle | Strings containing 3 consecutive numbers in increasing order Puzzle | SQL Interview Question

In this puzzle you have to find strings which contains number sequences. E.g. If you have a string like ‘1231786’ so in this string you have 123 in sequence so we need this row in the output otherwise we will exclude that row from the output. Also note that we will only have string of numbers only. Alphanumeric characters will not be there.

Please check the sample input and the expected output.

Sample Input

Expected Output

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

--

CREATE TABLE CONSECUTIVE
(
	Vals VARCHAR(100)
)
GO

INSERT INTO CONSECUTIVE VALUES
(1234),
(1112),
(1),
(1034),
(NULL)
GO

SELECT * FROM CONSECUTIVE
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

1 SOLUTION

SOLUTION – 1

--

/* In this solution we have created a new table called [pos] where we 
are storing all the combinations of 3 digits in consecutive numbers 
in increasing order*/

CREATE TABLE [dbo].[pos]
(
       [opt] [int] NULL
) 
GO

INSERT [dbo].[pos] ([opt]) VALUES (123)
GO
INSERT [dbo].[pos] ([opt]) VALUES (234)
GO
INSERT [dbo].[pos] ([opt]) VALUES (345)
GO
INSERT [dbo].[pos] ([opt]) VALUES (456)
GO
INSERT [dbo].[pos] ([opt]) VALUES (567)
GO
INSERT [dbo].[pos] ([opt]) VALUES (678)
GO
INSERT [dbo].[pos] ([opt]) VALUES (789)
GO

/*After that we just have to JOIN this table with the input table to match the things*/


SELECT VALS from consecutive  c
CROSS JOIN pos 
WHERE CHARINDEX(CAST(opt AS VARCHAR(50)),vals) > 0 
GROUP BY VALS

--

Output-1

--                            

VALS
---------------- 
1234

(1 row affected)

--

SOLUTION – 2 | Using Recursion – Solution BY Ashu S

--

;With cte as 
(
	select vals as a, vals as b from consecutive
	union all
	select a, SUBSTRING(b,2,len(b)-1) from cte
	where len(b)>3
)
select distinct a 
from cte where cast(cast(b as int)- cast (replicate(substring(b,1,1),len(b)) as int) 
as varchar(max)) like '12%'

--

Output-2

--                            

a
------------------ 
1234

(1 row 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.

Pawan Khowal

Pawan is a SQL Server Developer. 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.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com