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

Expected Output

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