SQL Puzzle | Extract Integer Present at the start of the string in a column

In this puzzle In the puzzle we have to extract the integer value present at the start of the string in a column. 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 GetIntegers
(
	 Id INT
	,VALS VARCHAR(100)
)
GO

INSERT INTO GetIntegers VALUES
 (1,'P1')
,(2,'1 - Hero')
,(3,'2 - Ramesh')
,(4,'3 - KrishnaKANT')
,(5,'21 - Avtaar')
,(6,'5Laila')
,(7,'6  MMT')
,(8,'7#7#')
,(9,'#')
,(10,'8')
,(11,'98')
,(12,'111')
,(13,NULL)
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

--

SELECT * , SUBSTRING(Vals, 0, PATINDEX('%[^0-9]%', VALS + '+') ) Integers
FROM GetIntegers

--

Output

--

/*------------------------
SELECT * , SUBSTRING(Vals, 0, PATINDEX('%[^0-9]%', VALS + '+') ) Integers
FROM GetIntegers
------------------------*/                     
Id          VALS                  Integers     
----------- --------------------- ------------ 
1           P1                                 
2           1 - Hero              1            
3           2 - Ramesh            2            
4           3 - KrishnaKANT       3            
5           21 - Avtaar           21           
6           5Laila                5            
7           6  MMT                6            
8           7#7#                  7            
9           #                                  
10          8                     8            
11          98                    98           
12          111                   111          
13          NULL                  NULL         
                                               
(13 row(s) affected)                           
                                               
                                                                                                  
                                             
--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com