Tags
Complex SQL Challenges, Complex TSQL Challenge, dynamic query, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
T-SQL Query | [ The Wild Card Puzzle ]
Puzzle Statement
- We have a single input table called “WildCard” contains columns Country, State and City
- We have to write a SP or Query which will accept “Country,State,City” arbitrarily and returns rows which are matching with these values.
- E.g. for input ‘Ind,Hary,Gu’ we have select rows which matches with these values.
- Note that we have to use Like operator.
- Sample input parameters are given below.
- ‘Ind,Haryana,Gu’
- ‘Ind,Har,Gu’
- ‘Ind’
- ‘I’
Also note that if only single value is present in the input parameter then we have search that value in Country column. If two are present then in country and state.
Please check out the sample input and expected output for details.
Sample Input
| Country | States | City |
| India | Haryana | Gurgaon |
| India | Haryana | Rewari |
| India | Haryana | Kanina |
| India | Haryana | Narnaul |
| India | Maharashtra | Nasik |
| India | Maharashtra | Mumbai |
| India | Maharashtra | Pune |
| India | Maharashtra | Nagar |
Expected output
(For input – ‘Ind,Hary,Gu’ )
| Country | States | City |
| India | Haryana | Gurgaon |
Rules/Restrictions
- Your solution should be should use “SELECT” statement or “CTE” or a Dynamic query.
- Your solution should be generic in nature.
- Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com
Script
Use the below script to generate the source table and fill them up with the sample data.
--
CREATE TABLE WildCard
(
Country VARCHAR(50)
,States VARCHAR(50)
,City VARCHAR(50)
)
GO
INSERT INTO WildCard VALUES
('India','Haryana','Gurgaon'),
('India','Haryana','Rewari'),
('India','Haryana','Kanina'),
('India','Haryana','Narnaul'),
('India','Maharashtra','Nasik'),
('India','Maharashtra','Mumbai'),
('India','Maharashtra','Pune'),
('India','Maharashtra','Nagar')
--
|
UPDATE – 23-Apr-2015 – Solution 1
--
SET NOCOUNT ON
DECLARE @Param AS VARCHAR(100) = 'Ind,Hary,Gu'
DECLARE @Cnt AS INT = 0
DECLARE @Qry AS VARCHAR(MAX) = ''
SET @Cnt = LEN(@Param) - LEN(REPLACE(@Param,',',''))
DECLARE @x AS XML=''
SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)
SELECT IDENTITY(INT,1,1) rnk , t.value('.', 'VARCHAR(100)') Params INTO #Params FROM @x.nodes('/A') AS x(t)
SET @Qry = ' SELECT Country,States,City FROM WildCard WHERE '
IF @Cnt = 0
SET @Qry = @Qry + ' Country LIKE ''%'' ' + '+' + '''' + (SELECT Params FROM #Params WHERE rnk = 1) + '''' + '+' + '''%'' '
ELSE IF @Cnt = 1
BEGIN
SET @Qry = @Qry + ' Country LIKE ''%'' ' + '+' + '''' + (SELECT Params FROM #Params WHERE rnk = 1) + '''' + '+' + '''%'' '
SET @Qry = @Qry + ' AND '
SET @Qry = @Qry + ' States LIKE ''%'' ' + '+' + '''' + (SELECT Params FROM #Params WHERE rnk = 2) + '''' + '+' + '''%'' '
END
ELSE IF @Cnt = 2
BEGIN
SET @Qry = @Qry + ' Country LIKE ''%'' ' + '+' + '''' + (SELECT Params FROM #Params WHERE rnk = 1) + '''' + '+' + '''%'' '
SET @Qry = @Qry + ' AND '
SET @Qry = @Qry + ' States LIKE ''%'' ' + '+' + '''' + (SELECT Params FROM #Params WHERE rnk = 2) + '''' + '+' + '''%'' '
SET @Qry = @Qry + ' AND '
SET @Qry = @Qry + ' City LIKE ''%'' ' + '+' + '''' + (SELECT Params FROM #Params WHERE rnk = 3) + '''' + '+' + '''%'' '
END
PRINT (@Qry)
EXEC (@Qry)
DROP TABLE dbo.#Params;
--
|
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning
Improving my SQL BI Skills 
DECLARE @InputElement VARCHAR(30)=’Ind,Hary,Gu’
BEGIN TRY
DECLARE @Country VARCHAR(100) = (SELECT SUBSTRING(@InputElement,1,charindex(‘,’,@InputElement)-1) AS Country)
DECLARE @StateCity VARCHAR(100)= (SELECT SUBSTRING(@InputElement,charindex(‘,’,@InputElement)+1,len(@InputElement)))
END TRY
BEGIN CATCH
SET @Country=@InputElement
END CATCH
BEGIN TRY
DECLARE @State VARCHAR(100)=(SELECT SUBSTRING(@StateCity,1,charindex(‘,’,@StateCity)-1) AS Country)
END TRY
BEGIN CATCH
SET @State=”
END CATCH
BEGIN TRY
DECLARE @City VARCHAR(100)=(SELECT SUBSTRING(@StateCity,charindex(‘,’,@StateCity)+1,len(@StateCity)))
END TRY
BEGIN CATCH
SET @StateCity=”
END CATCH
IF(@Country!=” and @State!=” and @City!=”)
BEGIN
SELECT * FROM
(
SELECT * FROM
(
SELECT * FROM WildCard WHERE Country LIKE ‘%’+@Country+’%’
)tbl1
WHERE States LIKE ‘%’+@State+’%’
)tbl2 WHERE City LIKE ‘%’+@City+’%’
END
ELSE IF(@Country!=” and @State=”)
BEGIN
SELECT * FROM
(
SELECT * FROM WildCard WHERE Country LIKE ‘%’+@Country+’%’
)tbl1 WHERE States LIKE ‘%’+@City+’%’
END
ELSE
BEGIN
SELECT * FROM WildCard WHERE Country LIKE ‘%’+@Country+’%’
END
LikeLike
Hi,
Thank you for the comment.
Pawan
LikeLike