Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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
Bash
cookie
Tooth
Click
Firewall

–Texts data

Texts
COOSGNDLOODINTIOIS
LNGMAKCEANDOROSRUC
IAEASPTLTAIPONRND
CNFIREWALLWREIKOOC
KFDPRDHTOOTHULBASH

Expected Output

Word Texts
Firewall CNFIREWALLWREIKOOC
Bash KFDPRDHTOOTHULBASH
Tooth KFDPRDHTOOTHULBASH

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

Advertisements