SQL Puzzle | The 2 Percentages Puzzle | SQL Interview Question

This is another very interesting puzzle. One of my developer asked this question to me. He got this question in a recently technical encounter he had with some other company :). Well here you need to find out records that starts with Pa%% and the number of %% should not cross 2. Meaning if we have 3 Pa%%% then that record should not selected. Please check out the sample input values and sample expected output below.

Sample Input

Id Val
1 Pa%
2 Pa%%an
3 Pa%%
4 Pa%%%
5 P%
6 Pa%%%%an
7 Pa%%%%an%%%%

Expected Output

Id Val
2 Pa%%an
3 Pa%%

Script – DDL and INSERT Sample Data

 ```-- CREATE TABLE getDoublePercentages ( Id INT ,Val VARCHAR(10) ) GO INSERT INTO getDoublePercentages VALUES (1,'Pa%'), (2,'Pa%%an'), (3,'Pa%%'), (4,'Pa%%%'), (5,'P%') GO INSERT INTO getDoublePercentages VALUES (6,'Pa%%%%an') GO SELECT * FROM getDoublePercentages GO -- ```

SOLUTION 1 | Using LIKE

 ```-- SELECT * FROM getDoublePercentages WHERE Val LIKE 'Pa[%][%]%' AND Val NOT LIKE 'Pa[%][%][%]%' -- ```

OUTPUT – 1 | Using LIKE

 ```-- Id Val ----------- ---------- 2 Pa%%an 3 Pa%% (2 rows affected) -- ```

SOLUTION 2 | Using LIKE AND LEN

 ```-- SELECT * FROM getDoublePercentages WHERE LEN(Val) - LEN(REPLACE(Val,'%','')) = 2 AND Val LIKE 'Pa[%][%]%' -- ```

OUTPUT – 2 | Using LIKE AND LEN

 ```-- Id Val ----------- ---------- 2 Pa%%an 3 Pa%% (2 rows affected) -- ```

Enjoy 🙂

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/