Tags

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


T-SQL Query | [ The Wild Card Puzzle ]

Puzzle Statement

  1. We have a single input table called “WildCard” contains columns Country, State and City
  2. We have to write a SP or Query which will accept “Country,State,City” arbitrarily and returns rows which are matching with these values.
  3. E.g. for input ‘Ind,Hary,Gu’ we have select rows which matches with these values.
  4. Note that we have to use Like operator.
  5. 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

http://MSBISkills.com

Advertisements