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