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
sanjay singh said:
;with cte
as
(
select ID,vals,PATINDEX(‘%.[0-9a-zA-Z]%’,vals) as vals1
from #testDots
union all
select id,vals,Vals1+PATINDEX(‘%.[0-9a-zA-Z]%’,SUBSTRING(vals,vals1+1,LEN(vals)))as vals1
from cte
where PATINDEX(‘%.[0-9a-zA-Z]%’,SUBSTRING(vals,vals1+1,LEN(vals)))>0
)
,cte1
as
(
select ID,vals,COUNT(vals1)as cnt from cte
group by ID,vals
)
select ID,Vals from cte1
where cnt>1
LikeLiked by 1 person
revank219 said:
select id,vals from testDots where
charindex(‘.’,vals,charindex(‘.’,vals)+3) 0
LikeLike