WHERE clause by Comma-Delimited String
-
Monday, May 14, 2012 4:22 PM
INPUT:
DECLARE @Location VARCHAR(MAX)
SELECT @Location = 'London,Bristol'
DECLARE @tLOCATION TABLE(
LOCATION_NAME VARCHAR(MAX)
)
INSERT INTO @tLOCATION (LOCATION_NAME) SELECT 'London'
INSERT INTO @tLOCATION (LOCATION_NAME) SELECT 'Bristol'
INSERT INTO @tLOCATION (LOCATION_NAME) SELECT 'Oxford'
Select * from @tLOCATIONOUTPUT:
LOCATION_NAME
London
Bristol
OxfordExpected Output:
LOCATION_NAME
London
BristolNow I would like to add a WHERE clause by Comma-Delimited String for @Location. I tried this and this did not work:
SELECT *
FROM @tLOCATION
WHERE LOCATION_NAME LIKE REPLACE(@Location, ',', '%')Any help would be really great.
monir
All Replies
-
Monday, May 14, 2012 4:26 PM
You will need to use a split function, which will return a table. If you check Naomi's post at the bottom of http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a562f5be-0457-47e6-b077-7b615465e370 you will see several split functions. Personally, I prefer the first one she linked.
Edit:
Using that particular split function, this code gives your desired output:
DECLARE @Location VARCHAR(MAX) SELECT @Location = 'London,Bristol' DECLARE @tLOCATION TABLE( LOCATION_NAME VARCHAR(MAX) ) INSERT INTO @tLOCATION (LOCATION_NAME) SELECT 'London' INSERT INTO @tLOCATION (LOCATION_NAME) SELECT 'Bristol' INSERT INTO @tLOCATION (LOCATION_NAME) SELECT 'Oxford' SELECT l.LOCATION_NAME FROM @tLOCATION l CROSS APPLY dbo.fncDelimitedSplit8K(@Location, ',') split WHERE l.LOCATION_NAME = split.Item
- Edited by dgjohnson Monday, May 14, 2012 4:30 PM
- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Monday, May 14, 2012 4:59 PM
- Unproposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Monday, May 14, 2012 5:00 PM
- Marked As Answer by Monir Hossain Tuesday, May 15, 2012 1:32 PM
-
Monday, May 14, 2012 5:02 PMModerator
Just a correction to the query using any of the split functions available:
select L.Location_Name from @tLocation L
where Location_Name IN (select Item from dbo.fncDelimitedSplit8K(@Location, ','))
Or you can use an INNER JOIN. CROSS APPLY syntax in this context is a bit confusing.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog- Marked As Answer by Monir Hossain Tuesday, May 15, 2012 1:30 PM
-
Monday, May 14, 2012 5:21 PMModerator
If you are using SS 2008 or greater, then try to avoid the delimited list and use a table-valued parameter if possible. Take a look to Erland's articles about Arrays and Lists in SQL Server, to learn different ways to shred the list.
AMB
- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Monday, May 14, 2012 5:37 PM
-
Monday, May 14, 2012 5:23 PMModerator
Just a correction to the query using any of the split functions available:
select L.Location_Name from @tLocation L
where Location_Name IN (select Item from dbo.fncDelimitedSplit8K(@Location, ','))
Or you can use an INNER JOIN. CROSS APPLY syntax in this context is a bit confusing.
For every expert, there is an equal and opposite expert. - Becker's Law
My blogThat would be a suggestion, more than a correction, IMHO.
AMB
-
Monday, May 14, 2012 5:31 PM
I could see how the CROSS APPLY would be confusing here (I am still only mildly comfortable with it myself, but happen to use it when I need to split things). As such, new example:
DECLARE @Location VARCHAR(MAX) SELECT @Location = 'London,Bristol' DECLARE @tLOCATION TABLE( LOCATION_NAME VARCHAR(MAX) ) INSERT INTO @tLOCATION (LOCATION_NAME) SELECT 'London' INSERT INTO @tLOCATION (LOCATION_NAME) SELECT 'Bristol' INSERT INTO @tLOCATION (LOCATION_NAME) SELECT 'Oxford' SELECT l.LOCATION_NAME FROM @tLOCATION l CROSS APPLY dbo.fncDelimitedSplit8K(@Location, ',') split WHERE l.LOCATION_NAME = split.Item SELECT l.LOCATION_NAME FROM @tLOCATION l Join dbo.fncDelimitedSplit8K(@Location, ',') split on l.LOCATION_NAME = split.Item
-
Monday, May 14, 2012 5:33 PMModeratorThe second variation is what I would use. You use CROSS APPLY when you need to apply function for the field in the table, in other words, if Location_Name field in the table will be a comma-delimited list of locations, this is where CROSS APPLY needs to be used and there is no other way to write that query.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
Tuesday, May 15, 2012 1:30 PM
Hi Naomi, Thanks for the reply. It did work with a little change in your sql: Select L.Lo...........(select Items from........n, ','). Means it is actually 'Items'. But really really thanks for your help.Just a correction to the query using any of the split functions available:
select L.Location_Name from @tLocation L
where Location_Name IN (select Item from dbo.fncDelimitedSplit8K(@Location, ','))
Or you can use an INNER JOIN. CROSS APPLY syntax in this context is a bit confusing.
For every expert, there is an equal and opposite expert. - Becker's Law
My blogmonir
-
Tuesday, May 15, 2012 1:31 PM
Thank you so much. I am trying this option too by using a Temporary table to avoid list. But list did work as 'Naomi' suggest :)If you are using SS 2008 or greater, then try to avoid the delimited list and use a table-valued parameter if possible. Take a look to Erland's articles about Arrays and Lists in SQL Server, to learn different ways to shred the list.
AMB
monir
-
Tuesday, May 15, 2012 3:06 PMModerator
It depends on the function you used. In Jeff Moden function the column's name is Item, I believe, not Items. "Items" in plural sounds a bit strange for the column name to be returned by splitting function.
Perhaps you need to show the function's code and your whole code just to verify.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
.png)
