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.
-
3Never store data as comma separated items, it will only cause you lot of trouble. One item per row!jarlh– jarlh2015-10-07 14:42:56 +00:00Commented Oct 7, 2015 at 14:42
-
1Is 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– Hambone2015-10-07 14:48:24 +00:00Commented Oct 7, 2015 at 14:48
-
1show us the query so we can help out we don't know what you did.DarkVision– DarkVision2015-10-07 14:49:24 +00:00Commented Oct 7, 2015 at 14:49
-
2Which 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– jarlh2015-10-07 14:55:07 +00:00Commented Oct 7, 2015 at 14:55
-
sry @nvoigt I didn't mentioned what i have tried because its a log story ;)R K Sharma– R K Sharma2015-10-07 15:07:07 +00:00Commented Oct 7, 2015 at 15:07
|
Show 1 more comment
1 Answer
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)
)
8 Comments
Scott Chamberlain
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.
Morpheus
Edited. Thank you for the suggestion.
Scott Chamberlain
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!
jarlh
Please specify which dbms this is for. (No product tagged in question.)
Morpheus
Edited to specify the dbms and show an applicable code example.
|