Tags

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


SQL Puzzle | The Comma Separated Puzzle

Puzzle Statement

I came to this puzzle while I was working on a bug. Here your have accept an input parameter which will accept comma separated values and search those values in a table.
E.g. if we are passing a,b then we should get 3 records.

Please go through the sample input and expected output for details.

Sample Input

1 a,b
2 a
3 b
4 c

Expected Output

For ‘a,b’ we should get below output.

1 a,b
2 a
3 b

Rules/RestrictionsThe solution should be should use “SELECT” statement or “CTE”.

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


--

CREATE TABLE TheCommaSplitPuzzle
(
	 Id SMALLINT
	,Value VARCHAR(100)
)
GO


INSERT INTO TheCommaSplitPuzzle
VALUES
(1,'a,b'),
(2,'a'),
(3,'b'),
(4,'c')
GO

--

SOLUTION 1 | Using CharIndex

--


DECLARE @Input AS VARCHAR(100) = 'a,b'

SELECT Value FROM TheCommaSplitPuzzle
WHERE 
	CHARINDEX(Value,@input,0) > 0

--

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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