SQL Puzzle | Word Search Puzzle

This puzzle is really simple. You have to write a query which will search all the strings from the words table from texts table. This was asked to me in a recent interview

Please check out the sample input and the expected output below-

Sample Input

–Strings to be searched

–Texts data

Expected Output

Rules/Restrictions

• The solution should be should use “SELECT” statement or a CTE.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data

--

CREATE TABLE Texts
(
	Texts VARCHAR(1000)
)
GO

INSERT INTO Texts VALUES
('COOSGNDLOODINTIOIS'),
('LNGMAKCEANDOROSRUC'),
('IAEASPTLTAIPONRND'),
('CNFIREWALLWREIKOOC'),
('KFDPRDHTOOTHULBASH')
GO

CREATE TABLE Words
(
	Texts VARCHAR(1000)
)
GO

INSERT INTO Words VALUES
('Bash'),
('cookie'),
('Tooth'),
('Click'),
('Firewall')
GO


--

Solution 1 | CROSS APPLY

--
SELECT Words.Texts Word , r.Texts Texts
FROM Words
CROSS APPLY
(
	SELECT Texts FROM Texts
)r
WHERE CHARINDEX(Words.Texts,r.Texts,0) > 0


--

Solution 2 | CROSS JOIN

--


SELECT Words.Texts Word , r.Texts Texts
FROM Words
CROSS JOIN Texts r
WHERE CHARINDEX(Words.Texts,r.Texts,0) > 0

--

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