0

I want to apply the SQL IN clause in my query for a names column. I know there is an option 'like' but, in the UI the user selects the names of customer from a multi-select dropdown, so I have a comma-separated list of names like 'rocky,joni,david' and to use SQL IN the list needs to be like 'rocky','joni','david'. Let me know if something is not clear in my question.

R K Sharma
  • 845
  • 8
  • 23
  • 42
  • 3
    Never store data as comma separated items, it will only cause you lot of trouble. One item per row! – jarlh Oct 07 '15 at 14:42
  • 1
    Is it always three values? Can it be more? Can it be 100 or 1000? It sounds like you're trying to generate dynamic SQL, which has some pitfalls you should understand. There may be better ways to handle it, depending on my questions above. – Hambone Oct 07 '15 at 14:48
  • 1
    show us the query so we can help out we don't know what you did. – DarkVision Oct 07 '15 at 14:49
  • 2
    Which dbms are you using? (You've already got one product specific answer, and it's not really fair to waste peoples' time writing answers for wrong product...) – jarlh Oct 07 '15 at 14:55
  • sry @nvoigt I didn't mentioned what i have tried because its a log story ;) – R K Sharma Oct 07 '15 at 15:07
  • you got it bro @Hambone but I have to handle it the way it is, client wants this, yes its dynamic sql query :( – R K Sharma Oct 07 '15 at 15:09

1 Answers1

4

For MS SQL Server, you can use a split function such as the following one found here: https://stackoverflow.com/a/10914602/3854195

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

Then you can use IN since the function returns your values as a table.

DECLARE @CustomerList varchar(max)

/* Populate @CustomerList with your comma separated values  */

SELECT *
FROM Customers
WHERE CustomerName IN (
        SELECT [Name]
        FROM dbo.splitstring(@CustomerList)
        )
Community
  • 1
  • 1
Morpheus
  • 1,616
  • 1
  • 21
  • 31
  • 2
    It would be good to add a reference copy of the script in case the link ever goes down (Questions and answers can get deleted after all). Be sure to still include a link to the source. – Scott Chamberlain Oct 07 '15 at 14:50
  • 1
    Edited. Thank you for the suggestion. – Morpheus Oct 07 '15 at 14:52
  • 1
    I would tweak that last code example you copied from the original post to actully use the `splitstring` function in a `IN`, other than that, good job! – Scott Chamberlain Oct 07 '15 at 14:54
  • Please specify which dbms this is for. (No product tagged in question.) – jarlh Oct 07 '15 at 14:55
  • Edited to specify the dbms and show an applicable code example. – Morpheus Oct 07 '15 at 15:15
  • you did before me it I was almost there, – R K Sharma Oct 07 '15 at 15:21
  • `ALTER FUNCTION [dbo].[fnSplitString] ( @string NVARCHAR(MAX), @delimiter CHAR(1) ) RETURNS @output TABLE(splitdata NVARCHAR(MAX) ) BEGIN DECLARE @start INT, @end INT SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) WHILE @start < LEN(@string) + 1 BEGIN IF @end = 0 SET @end = LEN(@string) + 1 INSERT INTO @output (splitdata) VALUES(SUBSTRING(@string, @start, @end - @start)) SET @start = @end + 1 SET @end = CHARINDEX(@delimiter, @string, @start) END RETURN END` – R K Sharma Oct 07 '15 at 15:23
  • @RKSharma I am glad that I could help. There were a lot of questions like which DBMS because it is usually one of the tags so we know which "flavor" of SQL we are dealing with. I think I automatically thought MS because of C#. :) – Morpheus Oct 07 '15 at 15:31