SQL Puzzle | Find rows with more than 2 dots(.)[Number of occurance of a charater in a string]

In this puzzle you need to find rows with more than 2 dots are present in the column Vals. For more details please check the sample input and expected output.

Sample Input

ID Vals
1 0.0
2 2.3.1.1
3 4.1.a.3.9
4 1.1.
5 a.b.b.b.b.b..b..b
6 6.

Expected Output

ID Vals
2 2.3.1.1
3 4.1.a.3.9
5 a.b.b.b.b.b..b..b

Script

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

--

CREATE TABLE testDots
(
	 ID INT
	,Vals VARCHAR(100)
)
GO

INSERT INTO testDots VALUES
(1,'0.0'),
(2,'2.3.1.1'),
(3,'4.1.a.3.9'),
(4,'1.1.'),
(5,'a.b.b.b.b.b..b..b'),
(6,'6.')
GO

--

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 testDots WHERE Vals LIKE '%.%.%.%'

--

Output-1

--                               
  
ID          Vals
----------- ----------------------------------------------------------------------------------------------------
2           2.3.1.1
3           4.1.a.3.9
5           a.b.b.b.b.b..b..b

(3 rows affected)


                                              
                                    
--

Solution – 2

--

SELECT * FROM testDots WHERE LEN(Vals) - LEN(REPLACE(Vals,'.','')) > 2

--

Output-2

--                               
  
ID          Vals
----------- ----------------------------------------------------------------------------------------------------
2           2.3.1.1
3           4.1.a.3.9
5           a.b.b.b.b.b..b..b

(3 rows 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