?
Solved

T-SQL: Stored Procedure: Compare table values with CSV string

Posted on 2012-12-20
5
Medium Priority
?
730 Views
Last Modified: 2012-12-27
Hi!

I need some help how to compare values from a table with a csv string.

1. I have a SP that takes a CSV string.
2. I need to check so the values from a SELECT in the SP  exists in the CSV string

The problem is that some values in the table must exists in the csv string but there are also values that are grouped, and just one of theses values needs to be found in the csv string.

EX:
CSV string
'foo1', 'foo2', foo3', 'foo5'

SELECT Query in SP will return
C1_Value     C2_Group
foo1             1
foo2             2
foo3             3
foo4             3
foo5             4

foo3 and foo4 are in the same group => OR between these values

SQL SP should verify:
is table value 'foo1' in csv string AND
is table value 'foo2' in csv string AND
is table value  ('foo3' OR 'foo4')  in csv  string AND
is table value 'foo5' in csv string

if true return 1 if false return 0

Any tips?
0
Comment
Question by:ulf-jzl
5 Comments
 
LVL 4

Expert Comment

by:MimicTech
ID: 38711215
This is pretty out there but hey fuel for the brain.

crazy idea:

select distinct C2_Group from Table
left outer join
(
  select
    C2_Group
  from Table
  group by C2_Group
  having charindex(C1_Value, CSV_STRING) > 0
) as groups
on groups.C2_Group = Table.C2_Group
where groups.C2_Group is null

Or something like that. Sorry if syntax is not exact. Just guessing here.

If the result set is empty then all groups should be represented. If it has results, then those are the ones that do not have a match.

Again, this is more as a idea generator as opposed to a full solution.
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38712157
Do as below...but check your performance....


DECLARE @tab TABLE (C1_Value VARCHAR(100), C2_Group VARCHAR(100))
DECLARE @csv_str NVARCHAR(500)

SET @csv_str = 'foo1, foo2, foo3, foo5'

SELECT @csv_str

INSERT INTO @tab (C1_Value, C2_Group)
SELECT 'foo1', 1
UNION ALL
SELECT 'foo2', 2
UNION ALL
SELECT 'foo4', 3
UNION ALL
SELECT 'foo5', 4

IF EXISTS (
		SELECT 1 is_grp_found
		FROM @tab
		GROUP BY C2_Group
		HAVING sum(CASE 
					WHEN CHARINDEX(c1_value, @csv_str, 1) > 0
						THEN 1
					ELSE 0
					END) = 0
		)
	SELECT 0  -- one or many grp not found 
ELSE
	SELECT 1  -- All group found 

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38712158
I have tested this code:
create table tmp_ee ( c1_value varchar(10), c2_group int )
go
insert into tmp_ee values('foo1',             1)
insert into tmp_ee values('foo2',             2)
insert into tmp_ee values('foo3',             3)
insert into tmp_ee values('foo4',             3)
insert into tmp_ee values('foo5',             4)
go

declare @csv varchar(1000)
set @csv ='foo1,foo2,foo3,foo5'

select x.c2_group, x.c1_value
 from tmp_ee x where x.c2_group not in ( 
select tmp_ee.c2_group
from dbo.ParmsToList(@csv, ',') f
join tmp_ee on c1_value = f.value
)

set @csv ='foo1,foo2,foo5'

select x.c2_group, x.c1_value
 from tmp_ee x where x.c2_group not in ( 
select tmp_ee.c2_group
from dbo.ParmsToList(@csv, ',') f
join tmp_ee on c1_value = f.value
)
go
drop table tmp_ee

Open in new window


the function dbo.ParmsToList is here:
http://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38722741
You just create string for single record of SQL table and compare with CSV file row one by one.
0
 

Author Closing Comment

by:ulf-jzl
ID: 38725636
Thx man, worked perfectly! :-)

And big thx to all of you.... :-)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question