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

Expected Output

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 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

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.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com