Tags

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


SQL Puzzle | The String format Puzzle ( int.int.int )

In this puzzle In the puzzle we have to data in a column called Vals. We have to fetch rows which are of format INT.INT.INT from vals column. Please check the sample input and expected output.

Sample Input

Id Vals
1 10.11.44
2 1.1
3 2.3.1
4 a.b.c
5 12..78
8 $.1.1
9 1aaa.2bbb.3ccc
10 1.2.3.4

Expected Output

Id Vals
1 10.11.44
3 2.3.1

Script

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

--

CREATE TABLE TheThreeDotsPuzzle 
(
	  Id INT
	 ,Vals VARCHAR(100)
)
GO

INSERT INTO TheThreeDotsPuzzle VALUES 
(1,'10.11.44')
,(2,'1.1')
,(3,'2.3.1')
,(4,'a.b.c')
,(5,'12..78')
,(8,'$.1.1')
,(9,'1aaa.2bbb.3ccc')
,(10,'1.2.3.4')
GO

SELECT * FROM TheThreeDotsPuzzle

--

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 * from TheThreeDotsPuzzle WHERE Vals 
LIKE '[0-9]%.[0-9]%.[0-9]%' AND TRY_CAST( REPLACE(Vals,'.','') AS BIGINT) IS NOT NULL AND DATALENGTH(Vals)  - DATALENGTH(REPLACE(Vals,'.','')) = 2

--

Solution – 2

--

Select * from TheThreeDotsPuzzle WHERE Vals 
LIKE '[0-9]%.[0-9]%.[0-9]%' AND REPLACE(Vals,'.','') NOT LIKE '%[^0-9]%' AND DATALENGTH(Vals)  - DATALENGTH(REPLACE(Vals,'.','')) = 2

--

Output

--

/*------------------------
Select * from TheThreeDotsPuzzle WHERE Vals 
LIKE '[0-9]%.[0-9]%.[0-9]%' AND REPLACE(Vals,'.','') NOT LIKE '%[^0-9]%' AND DATALENGTH(Vals)  - DATALENGTH(REPLACE(Vals,'.','')) = 2
------------------------*/
Id          Vals
----------- ----------------------------------------------------------------------------------------------------
1           10.11.44
3           2.3.1

(2 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