Tags

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


SQL Puzzle | Get data till Integer Value starting integer Value

In this puzzle you need to fetch data till Integer Value. For more details please check the sample input and expected output.

Sample Input

Vals
–17–
-1—
-56—
-191—

Expected Output

Vals TillIntegerData
–17– –17
-1— -1
-56— -56
-191— -191

Script

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

--

CREATE TABLE GetTillIntegerData
(
	Vals VARCHAR(100)
)
Go

INSERT INTO GetTillIntegerData VALUES
('--17--'),
('-1---'), 
('-56---'), 
('-191---') 
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,
LEFT(Vals,PATINDEX('%[0-9]%',Vals) 
	+ PATINDEX('%[^0-9]%',SUBSTRING(Vals,PATINDEX('%[0-9]%',Vals),LEN(Vals)))-2)
TillIntegerData
FROM GetTillIntegerData

--

Output-1

--

/*------------------------
SELECT Vals,
LEFT(Vals,PATINDEX('%[0-9]%',Vals) 
	+ PATINDEX('%[^0-9]%',SUBSTRING(Vals,PATINDEX('%[0-9]%',Vals),LEN(Vals)))-2)
TillIntegerData
FROM GetTillIntegerData                            
------------------------*/                         
Vals                            TillIntegerData    
------------------------------- ------------------ 
--17--                          --17               
-1---                           -1                 
-56---                          -56                
-191---                         -191               
                                                   
(4 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