Matching another table value with comma separator

Mohammad Farook 161 Reputation points


create table #temp1 (ID INT,Tags varchar(100))
insert into #temp1 (ID,tags) VALUES (101,'AAA'),(102,'BBB'),(103,'CCC')

create table #temp (Level INT,Condition varchar(100),TName VARCHAR(200))

INSERT INTO #temp (Level,Condition,TName)
VALUES (1,'L1','AAA'),(2,'L2','BBB,CCC'),(3,'[L3] OR [L3_1]','AAA,BBB,CCC')

select * from #temp
drop table #temp,#temp1

I want exact result like below

Level Condition TName
1 L1 101
2 L2 102,103
3 [L3] OR [L3_1] 101,102,103

0 comments No comments
{count} votes

Accepted answer
  1. Tom Phillips 17,631 Reputation points
    from #temp t
        CROSS APPLY string_split(t.TName,',') s
        INNER JOIN #temp1 t1
        ON t1.tags = s.[value]
    GROUP BY t.[Level], t.[Condition]
    ORDER BY t.[Level], t.[Condition]
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Tom Cooper 8,441 Reputation points

    Two solutions depending on what you mean by "exact result".

    If you don't care about the order of the tags value, for example, if is it OK if your result reversed the tags on Level 2 so it looked like

    2 L2 103,102

    If that's OK, then you can do

    Select t.Level, t.Condition, String_Agg(t1.ID, ',')
    From #temp t
    Cross Apply String_Split(t.TName, ',') s
    Inner Join #temp1 t1 On s.value = t1.Tags
    Group By t.Level, t.Condition
    Order By Level;

    But if the strings must always show the tags in the order from TNAME, then you will need to create a split function which preserves the order of the items. The builtin function String_Split used in the above doesn't ALWAYS do that. So then you solution would look like, first create the user function

    CREATE FUNCTION dbo.Split (@DelimitedString nvarchar(max), @Delimiter nvarchar(max))
    RETURNS table
    /* Use Option(MaxRecursion 0) in queries that call this function if 
       there can be more than 99 delimited values in @DelimitedString */
    RETURN (
        WITH Pieces (ID, start, stop) AS (
          SELECT CAST(1 AS bigint), CAST(1 AS bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString) AS bigint)
          UNION ALL
          SELECT ID + 1, CAST(stop + DATALENGTH(@Delimiter)/2 As bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString, stop + DATALENGTH(@Delimiter)/2) AS bigint)
          FROM Pieces
          WHERE stop > 0
        SELECT ID,
          SUBSTRING(@DelimitedString, start, CASE WHEN stop > 0 THEN stop-start ELSE LEN(@DelimitedString) END) AS Element
        FROM Pieces

    Then you can do

    Select t.Level, t.Condition, String_Agg(t1.ID, ',') Within Group (Order By s.ID)
    From #temp t
    Cross Apply dbo.Split(TName, ',') s
    Inner Join #temp1 t1 On s.Element = t1.Tags
    Group By t.Level, t.Condition
    Order By Level;


    0 comments No comments

  2. MelissaMa-MSFT 24,136 Reputation points

    Hi @Mohammad Farook ,

    Please also refer below:

    SELECT Level,Condition,STRING_AGG(,',') TName  
    FROM #temp1 AS t1  
    INNER JOIN #temp AS t2  
    ON ',' + t2.TName + ',' LIKE '%,' + CONVERT(VARCHAR(250),t1.Tags) + ',%'  
    group by Level,Condition  
    order by Level  


    Level	Condition	TName  
    1	L1	101  
    2	L2	102,103  
    3	[L3] OR [L3_1]	101,102,103  

    Best regards,

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments