November 2, 2015 at 9:55 pm
I have a split string function that will take a comma delimited string and give back a table with all the values.
I have a table that has a column with a comma delimited comma delimited list of states.
I can use a where clause to find one state in the table (such as all records that have CA in the states string).
But need to find out how to find all the rows that have all or any of the states from a comma delimited parameter.
Here is the schema
CREATE FUNCTION [dbo].[split] (@list nvarchar(MAX))
RETURNS @tbl TABLE (svar nvarchar(10) NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (svar)
VALUES (substring(@list, @pos + 1, @valuelen))
SELECT @pos = @nextpos
END
RETURN
END
GO
CREATE TABLE [dbo].[CSVTest](
[CSVTestid] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[States] [varchar](50) NULL,
)
GO
INSERT CSVTest(Name, States) Values('Joe','CA,FL,HI')
INSERT CSVTest(Name, States) Values('Mark','FL,MI')
INSERT CSVTest(Name, States) Values('Greg','MI,AR,AL')
INSERT CSVTest(Name, States) Values('Mary','AL')
GO
Here is a query that works with one state. But how do I get it work work the 2nd select (which is commented out and has two states?
declare @stemp varchar(50)
select @stemp = 'mi'
--select @stemp = 'fl,al'
select *
FROM CSVTest c
WHERE @stemp in
(select svar
from split(c.States))
Thanks,
Tom
November 2, 2015 at 10:15 pm
Quick note, your design is not so great if you work on large data set. i would recommend that data should be already parsed for states.
you will eventually get into to change it if not now.
Anyways, for the solution you can refer to that
declare @stemp varchar(50)
--select @stemp = 'mi'
select @stemp = 'fl,al'
;
WITH cStemp
AS
(
select svar as Stemps
from split(@stemp)
)
select c.*
FROM CSVTest c
cross apply
(
select svar as states
from split(c.States)
)xx
where exists ( Select 1
from cStemp c
where xx.states = c.Stemps
)
This code will work for both i-e single value or comma separated list. Hope it helps.
November 2, 2015 at 11:39 pm
I agree on the design. Unfortunately, this isn't my design and I am working with someone else's database.
Your solution works except that it will not filter out duplicates?
select @stemp = 'fl,mi'
Will give you:
1JoeCA,FL,HI
2MarkFL,MI
2MarkFL,MI
3GregMI,AR,AL
I can fix that by changing the query after the CTE to:
select distinct c.*
FROM CSVTest c
cross apply
...
This would only give me one row (Mark) back as that is the only one with both states or if @stemp = "CA,HI", you would only get Joe back as he is the only one with both CA and HI.
Thanks,
Tom
November 3, 2015 at 12:05 am
This gives me any row that has any of the states in the @stemp variable.
Can the query be changed to give back only rows that have ALL the states in the @stemp variable.
Thanks,
Tom
November 3, 2015 at 12:22 am
tshad (11/3/2015)
This gives me any row that has any of the states in the @stemp variable.Can the query be changed to give back only rows that have ALL the states in the @stemp variable.
Thanks,
Tom
You requirement are not so clear, can you please share an example what is you really looking for it will help to understand the actual problem.
Also share the working in case of single State in @stemp variable what will be the desired output.
November 3, 2015 at 12:59 am
Try this query which utilises Left Anti Semi Join in its execution plan
--All the rows that have all of the states from a comma delimited parameter.
declare @stemp varchar(50);
select @stemp = 'fl,ca';
with cStemp AS (
select svar as Stemps
from split(@stemp)
)
select c.*
from CSVTest c
outer apply (
select top(1) notFound=Stemps
from (
select Stemps from cStemp c
except
select svar from split(c.States)
) xxx
)xx
where xx.notFound is null;
November 3, 2015 at 1:06 am
Ok.
The same table and split function as above.
The following Insert statements.
INSERT CSVTest(Name, States) Values('Joe','CA,FL,HI')
INSERT CSVTest(Name, States) Values('Mark','FL,MI')
INSERT CSVTest(Name, States) Values('Greg','MI,AR,AL')
INSERT CSVTest(Name, States) Values('Mary','AL')
INSERT CSVTest(Name, States) Values('Frank','AL,AR,WI')
Select @stemp = 'AR,AL'
This should return only Greg and Frank as they are the only ones with both "AR" and "AL"
Not sure what you meant by the last statement. But if you meant what would I want to see if there were a single State in the @stemp variable, then if @stemp were equal to "FL", I would expect to get "Joe" and "Mark" back.
Thanks,
Tom.
November 3, 2015 at 1:19 am
Serg,
That also works for my ALL question.
Serg and twin.devil,
I was looking at both solutions as I am not sure which I will need until tomorrow.
They both help out a lot.
Thanks for the help,
Tom
November 3, 2015 at 1:43 am
tshad (11/3/2015)
I was looking at both solutions as I am not sure which I will need until tomorrow.
Ok, then count them and decide.
declare @stemp varchar(50);
select @stemp = 'fl,ca';
with cStemp AS (
select svar as Stemps
from split(@stemp)
)
select c.*,xx.*
from CSVTest c
cross apply (
select count(cs.svar) nMatch
from cStemp c
left join split(c.States) cs
on c.Stemps = cs.svar
)xx
where
-- at least one
--xx.nMatch > 0;
-- all
--xx.nMatch=(select count(*) from cStemp);
-- half or more
(xx.nMatch+ 0. )/(select count(*) from cStemp) >=0.5
November 3, 2015 at 1:44 am
My 2 cents
declare @stemp varchar(50) = 'fl,ca'
create table #csvtest (name varchar(5), states varchar(10))
INSERT #csvtest(Name, States) Values('Joe','CA,FL,HI')
INSERT #csvtest(Name, States) Values('Mark','FL,MI')
INSERT #csvtest(Name, States) Values('Greg','MI,AR,AL')
INSERT #csvtest(Name, States) Values('Mary','AL')
INSERT #csvtest(Name, States) Values('Frank','AL,AR,WI')
select * from #csvtest
SELECT
DISTINCT
name
FROM
#csvtest t
CROSS APPLY
dbo.DelimitedSplit8K(t.states,',') sp
WHERE
sp.item in (select item from dbo.DelimitedSplit8K(@stemp,','))
drop table #csvtest
Note you will need to get delimitedsplit8k from the string splitter link in my signature
November 3, 2015 at 2:37 am
They all look pretty good and solve my problem.
Thanks a lot.
Tom
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply