Solved

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

Posted on 2012-12-20

1 solution

Medium Priority

706 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?

Comment

Question by:ulf-jzl

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

5 Comments

LVL 4

Expert Comment

by:MimicTech

ID: 387112152012-12-20

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.

LVL 12

Expert Comment

by:Saurabh Bhadauria

ID: 387121572012-12-20

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

LVL 143

Accepted Solution

by:Guy Hengel [angelIII / a3]

Guy Hengel [angelIII / a3] earned 2000 total points

ID: 387121582012-12-20

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

LVL 21

Expert Comment

by:Alpesh Patel

ID: 387227412012-12-26

You just create string for single record of SQL table and compare with CSV file row one by one.

Author Closing Comment

by:ulf-jzl

ID: 387256362012-12-27

Thx man, worked perfectly! :-)

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

Featured Post

Question has a verified solution.

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

Step by step guide to take SQL Server 2012 new feature Available Groups (Hadr) to Production

Article by: Duane

Why is this different from all of the other step by step guides? Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…

Microsoft SQL Server

How to replicate SQL 2008 database to SQL 2014 by using Initialize from Backup.

Article by: lcohan

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…

Executing a SQL Server Function from Within Access

Video by: TechMommy

Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

Creating and Executing a SQL Pass-thru Query

Video by: TechMommy

Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…