Sql: Parse Comma-Delimited String and Use as Join

How to join comma separated column values with another table as rows

Assuming you're using SQL Server 2016, you can use string_split() to parse out your CSV column (aside: comma-separated values in a field is a sign of a poor data model) without resorting to a CTE or XML methods.

select I.inquiry_id, sup.value,V.Name
from Procure_InquiryDetails I
CROSS APPLY string_split(I.supplier_value,',') sup
join Vendor v on v.DCLink = sup.value

SQL: Parse Comma-delimited string and use as join

Based on this answer you can do something like this

select *
from table1 t1 join table2 t2 on ','||t1.id||',' like '%,'||t2.id||',%'

here is a sqlfiddle

sql join on comma separated column

Gordon Linoff is right for the row level data storage instead of comma-separate separated.
If you have used comma separated data and size of the table is high then i have one more solution with good query performance for MS SQL Server.

Make your data comma-separated to list and then use JOIN with your required tables.

 SELECT A.OtherID,  
     Split.a.value('.', 'VARCHAR(100)') AS Data  
 FROM  
 (
     SELECT OtherID,  
         CAST ('<M>' + REPLACE(Data, ',', '</M><M>') + '</M>' AS XML) AS Data  
     FROM  Table1
 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a); 

Splitting comma-separated string selecting as columns with joins in SQL Server?

Some tricks with xml and then pivoting:

DECLARE @t TABLE
    (
      name VARCHAR(20) ,
      orderid VARCHAR(20)
    )

INSERT  INTO @t
VALUES  ( 'chao', '1,2,3' ),
        ( 'bambino', '4,5,6,7,8' );
WITH    cte
          AS ( SELECT   name ,
                        Split.a.value('.', 'VARCHAR(100)') AS orderid ,
                        ROW_NUMBER() OVER ( PARTITION BY name ORDER BY ( SELECT
                                                              NULL
                                                              ) ) rn
               FROM     ( SELECT    name ,
                                    CAST ('<M>' + REPLACE(orderid, ',',
                                                          '</M><M>') + '</M>' AS XML) AS orderid
                          FROM      @t
                        ) AS A
                        CROSS APPLY orderid.nodes('/M') AS Split ( a )
             )
    SELECT  name ,
            [1] AS order1 ,
            [2] AS order2 ,
            [3] AS order3 ,
            [4] AS order4 ,
            [5] AS order5 ,
            [6] AS order6 ,
            [7] AS order7 ,
            [8] AS order8 ,
            [9] AS order9 ,
            [10] AS order10
    FROM    cte PIVOT( MAX(orderid) FOR rn IN ( [1], [2], [3], [4], [5], [6],
                                                [7], [8], [9], [10] ) ) p

Output:

name    order1  order2  order3  order4  order5  order6  order7  order8  order9  order10
bambino 4       5       6       7       8       NULL    NULL    NULL    NULL    NULL
chao    1       2       3       NULL    NULL    NULL    NULL    NULL    NULL    NULL

Joining a table based on comma separated values

Maybe this uglyness, I have not checked results:

select names.name, courses.course_name
from names inner join courses
    on ',' + names.course_ids + ',' like '%,' + cast(courses.course_id as nvarchar(20)) + ',%'

join comma delimited data column

Ideally, your best solution would be to normalize Table2 so you are not storing a comma separated list.

Once you have this data normalized then you can easily query the data. The new table structure could be similar to this:

CREATE TABLE T1
(
  [col1] varchar(2), 
  [col2] varchar(5),
  constraint pk1_t1 primary key (col1)
);

INSERT INTO T1
    ([col1], [col2])
VALUES
    ('C1', 'john'),
    ('C2', 'alex'),
    ('C3', 'piers'),
    ('C4', 'sara')
;

CREATE TABLE T2
(
  [col1] varchar(2), 
  [col2] varchar(2),
  constraint pk1_t2 primary key (col1, col2),
  constraint fk1_col2 foreign key (col2) references t1 (col1)
);

INSERT INTO T2
    ([col1], [col2])
VALUES
    ('R1', 'C1'),
    ('R1', 'C2'),
    ('R1', 'C4'),
    ('R2', 'C3'),
    ('R2', 'C4'),
    ('R3', 'C1'),
    ('R3', 'C4')
;

Normalizing the tables would make it much easier for you to query the data by joining the tables:

select t2.col1, t1.col2
from t2
inner join t1
  on t2.col2 = t1.col1

See Demo

Then if you wanted to display the data as a comma-separated list, you could use FOR XML PATH and STUFF:

select distinct t2.col1, 
  STUFF(
         (SELECT distinct ', ' + t1.col2
          FROM t1
          inner join t2 t
            on t1.col1 = t.col2
          where t2.col1 = t.col1
          FOR XML PATH ('')), 1, 1, '') col2
from t2;

See Demo.

If you are not able to normalize the data, then there are several things that you can do.

First, you could create a split function that will convert the data stored in the list into rows that can be joined on. The split function would be similar to this:

CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return 
end;

When you use the split, function you can either leave the data in the multiple rows or you can concatenate the values back into a comma separated list:

;with cte as
(
  select c.col1, t1.col2
  from t1
  inner join 
  (
    select t2.col1, i.items col2
    from t2
    cross apply dbo.split(t2.col2, ',') i
  ) c
    on t1.col1 = c.col2
) 
select distinct c.col1, 
  STUFF(
         (SELECT distinct ', ' + c1.col2
          FROM cte c1
          where c.col1 = c1.col1
          FOR XML PATH ('')), 1, 1, '') col2
from cte c

See Demo.

A final way that you could get the result is by applying FOR XML PATH directly.

select col1, 
(
  select ', '+t1.col2
  from t1
  where ','+t2.col2+',' like '%,'+cast(t1.col1 as varchar(10))+',%'
  for xml path(''), type
).value('substring(text()[1], 3)', 'varchar(max)') as col2
from t2;

See SQL Fiddle with Demo

How to split a comma-separated value to columns

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

---

Related Topics