Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

Id VALS
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

Expected Output

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

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

Advertisements