SQL Puzzle | Get Integer data from a string column puzzle

In this puzzle you need to fetch Integer data from a string column. For more details please check the sample input and expected output.

Sample Input

Vals
–17hfdssd–
-213121—
-56—
-191—
kjds
82376432

Expected Output

Vals IntegerData
–17hfdssd– 17
-213121— 213121
-56— 56
-191— 191
kjds
82376432 82376432

Script

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

 ```-- CREATE TABLE ExtarIntegerData ( Vals VARCHAR(100) ) Go INSERT INTO ExtarIntegerData VALUES ('--17hfdssd--'), ('-213121---'), ('-56---'), ('-191---'), ('kjds'), ('82376432') 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 Vals,SUBSTRING(vals,StartIndex,EndIndex) IntegerData FROM ( SELECT Vals, PATINDEX('%[0-9]%',Vals) StartIndex, CASE WHEN PATINDEX('%[^0-9]%',SUBSTRING(Vals,PATINDEX('%[0-9]%',Vals),LEN(Vals))) = 0 THEN LEN(Vals) ELSE PATINDEX('%[^0-9]%',SUBSTRING(Vals,PATINDEX('%[0-9]%',Vals),LEN(Vals)))-1 END EndIndex FROM ExtarIntegerData )m -- ```

Output-1

 ```-- /*------------------------ SELECT Vals,SUBSTRING(vals,StartIndex,EndIndex) IntegerData FROM ( SELECT Vals, PATINDEX('%[0-9]%',Vals) StartIndex, CASE WHEN PATINDEX('%[^0-9]%',SUBSTRING(Vals,PATINDEX('%[0-9]%',Vals),LEN(Vals))) = 0 THEN LEN(Vals) ELSE PATINDEX('%[^0-9]%',SUBSTRING(Vals,PATINDEX('%[0-9]%',Vals),LEN(Vals)))-1 END EndIndex FROM ExtarIntegerData )m ------------------------*/ Vals IntegerData -------------------------------------------------- --17hfdssd-- 17 -213121--- 213121 -56--- 56 -191--- 191 kjds 82376432 82376432 (6 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