Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.
Course Of The Month
6 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
T-SQL: Stored Procedure: Compare table values with CSV string
Posted on 2012-12-20
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?
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?
[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
5 Comments
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.
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.
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
I have tested this code:
the function dbo.ParmsToList is here:
http://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html
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
the function dbo.ParmsToList is here:
http://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on.
…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses
Course of the Month6 days, 2 hours left to enroll
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.