I am having a challenge comparing values in Available column with values in Required column. They are both comma separated.

What I want to achieve is, if values in the Required column are all found in the Available column then it gives me a match of 1 and 0 if one or more values that are in the Required column is missing in the Available column

I want to achieve this in SQL.

CodePudding user response：

If I understand the question correctly, an approach based on STRING_SPLIT() and an appropriate JOIN is an option:

Sample data:

SELECT *
INTO Data 
FROM (VALUES
   ('One, Two, Three', 'One, Three'),
   ('One, Three',      'Three, Five'),
   ('One, Two, Three', 'Two')
) v (Available, Required)

Statement:

SELECT 
   Available, Required,
   CASE
      WHEN EXISTS (
         SELECT 1
         FROM STRING_SPLIT(Required, ',') s1
         LEFT JOIN STRING_SPLIT(Available, ',') s2 ON TRIM(s1.[value]) = TRIM(s2.[value])
         WHERE s2.[value] IS NULL      
      ) THEN 0
      ELSE 1
   END AS Match
FROM Data

Result:

Available       Required    Match
---------------------------------
One, Two, Three One, Three  1
One, Three      Three, Five 0
One, Two, Three Two         1

CodePudding user response：

You need to do a cross join to look in all available values, your query would be :

    SELECT t.*
    ,case when SUM(CASE 
        WHEN t1.Available LIKE '%'   t.Required   '%'
            THEN 1
        ELSE 0
        END) > 0  THEN 1  ELSE 0 END AS [Match_Calculated]
FROM YOUR_TABLE t
CROSS JOIN YOUR_TABLE  t1
GROUP BY t.Available
    ,t.Required
    ,t.Match

Here's a dbfiddle

CodePudding user response：

You can use "STRING_SPLIT" to achieve your request

;with Source as 
(
    select 1 id,'One,Two,Three' Available,'One,Three' Required
    union all
    select 2 id,'One,Three' Available,'Three,Five' Required
    union all
    select 3 id,'One,Two,Three' Available,'Two' Required
)
,AvailableTmp as 
(
    SELECT t.id,
            x.value
       FROM Source t
            CROSS APPLY (SELECT trim(value) value
                                FROM string_split(t.Available, ',')) x
)
,RequiredTmp as 
(
SELECT t.id,
       x.value
       FROM Source t
            CROSS APPLY (SELECT trim(value) value
                                FROM string_split(t.Required, ',')) x
)
,AllMatchTmp as 
(
select a.id
        ,1 Match
    From RequiredTmp a 
        left join AvailableTmp b on a.id=b.id and a.value = b.value 
    group by a.id
    having max(case when b.value is null then 1 else 0 end ) = 0
)
select a.id
        ,a.Available
        ,a.Required
        ,ISNULL(b.Match,0) Match
    from Source a
        left join AllMatchTmp b on a.id = b.id