Tags

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


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

ID ipadr rating
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

Expected Output

ID ipadr rating
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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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)
ID          ipadr        rating
----------- ------------ --------------------------------------------------
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