SQL Puzzle | The 3 Integers 2 Dots Puzzle

In this puzzle you have to read a column called ipadr and find out data which is in the format of Interger.Integer.Integer. Note that the decimal points are mandatory in this case. Please check out the sample input values and sample expected output below.

Sample Input

1 10.11.44 OK
2 1.1 not enough points
3 2.3.1 OK
4 a.b.c letters instead of numbers
5 12..78 wrong – two points
8 \$.1.1 Ok
10 1.2.3.4 bad, too many periods

Expected Output

1 10.11.44 OK
3 2.3.1 OK

Script

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

 ```-- Declare @test As Table(ID int, ipadr Varchar(100), rating Varchar(50)); INSERT INTO @test VALUES (1,'10.11.44','OK') , (2,'1.1','not enough points') , (3,'2.3.1','OK') , (4,'a.b.c','letters instead of numbers') , (5,'12..78','wrong - two points') , (8,'\$.1.1','Ok') , (9,'1aaa.2bbb.3ccc','bad, letters') , (10,'1.2.3.4','bad, too many periods') -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1

 ``` -- Select * from @test WHERE ipadr LIKE '[0-9]%.[0-9]%.[0-9]%' AND TRY_CAST( REPLACE(ipadr,'.','') AS BIGINT ) IS NOT NULL AND DATALENGTH(ipadr) - DATALENGTH(REPLACE(ipadr,'.','')) = 2 -- ```

Solution – 1

 ``` -- SELECT Id,ipadr,rating FROM ( SELECT * , SUBSTRING( ipadr, 0 , CHARINDEX ( '.' , ipadr , 0 ) ) a ,SUBSTRING(ipadr, CHARINDEX ( '.' , ipadr , 0 )+1 , CHARINDEX ( '.', ipadr ,CHARINDEX ( '.' , ipadr , 0 ) )-1 ) b ,SUBSTRING(ipadr, CHARINDEX ( '.', ipadr ,CHARINDEX ( '.' , ipadr , 0 ))+ LEN(SUBSTRING(ipadr, CHARINDEX ( '.' , ipadr , 0 )+1 , CHARINDEX ( '.', ipadr ,CHARINDEX ( '.' , ipadr , 0 ) )-1 ))+2, LEN(ipadr) ) c FROM @test )t WHERE TRY_CAST(a AS BIGINT) IS NOT NULL AND TRY_CAST(b AS BIGINT) IS NOT NULL AND TRY_CAST(c AS BIGINT) IS NOT NULL AND DATALENGTH(ipadr) - DATALENGTH(REPLACE(ipadr,'.','')) = 2 -- ```

Output

```--

/*------------------------
OUTPUT
------------------------*/

(8 row(s) affected)
----------- ------------ --------------------------------------------------
1           10.11.44     OK
3           2.3.1        OK

(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