SQL Puzzle | Find SQL but NOT with T SQL keyword Puzzle

In this puzzle you have to find records where Vals column contains SQL keyword and the same record should not contain T SQL keyword. Please check the sample input and the expected output. The challenge is to do this in a single select

Welcome12$

Sample Input

Id Vals
1 Pawan likes Only T SQL.
2 Pawan like T SQL AND Oracle SQL.
3 Pawan like T SQL and little my SQL
4 Pawan Like LegiTest and SQL Server
5 Pawan Like Puzzles
6 Pawan likes T SQL and little cookies
7 T SQL T SQL

Expected Output

Id Vals
2 Pawan like T SQL AND Oracle SQL.
3 Pawan like T SQL and little my SQL
4 Pawan Like LegiTest and SQL Server
6 Pawan likes T SQL and little SQL

Script

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

--

CREATE TABLE FindMe
(
	 Id INT 
	,Vals NVARCHAR(200)
)
GO

INSERT INTO FindMe VALUES  
 (1,N'Pawan likes Only T SQL.')
,(2,N'Pawan like T SQL AND Oracle SQL.')
,(3,N'Pawan like T SQL and little my SQL')
,(4,N'Pawan Like LegiTest and SQL Server')
,(5,N'Pawan Like Puzzles')
,(6,N'Pawan likes T SQL and little cookies')
,(7,N'T SQL T SQL')
GO

SELECT * FROM FindMe
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 | This solution was given by my friend ASHUTOSH

--


SELECT * FROM FindMe 
WHERE CHARINDEX ('SQL' , REPLACE(Vals,'T SQL','-9'))  > 0


--

Output-1

--                            

Id          Vals
----------- --------------------------------------------
2           Pawan like T SQL AND Oracle SQL.
3           Pawan like T SQL and little my SQL
4           Pawan Like LegiTest and SQL Server
6           Pawan likes T SQL and little SQL

(4 rows affected)

--

SOLUTION – 2

--


SELECT *
FROM FindMe
WHERE  (LEN(Vals)-LEN(REPLACE(Vals,'T SQL','')))/DATALENGTH('T SQL') 
       != (LEN(Vals)-LEN(REPLACE(Vals,'SQL','')))/DATALENGTH('SQL')


--

Output-2

--                            

Id          Vals
----------- ---------------------------------------
2           Pawan like T SQL AND Oracle SQL.
3           Pawan like T SQL and little my SQL
4           Pawan Like LegiTest and SQL Server
6           Pawan likes T SQL and little SQL

(4 rows affected)

--

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Author Introduction: Pawan Khowal

Pawan is a SQL Server Expert. 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 Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ 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/

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

Advertisements