Tags

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


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