ulf-jzl
asked on
T-SQL: Stored Procedure: Compare table values with CSV string
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?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You just create string for single record of SQL table and compare with CSV file row one by one.
ASKER
Thx man, worked perfectly! :-)
And big thx to all of you.... :-)
And big thx to all of you.... :-)
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.