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

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