sql join on comma separated column

Question

It should be an easy one

My data is

TABLE1
AccreditationStatus         FacilityId      FacilityName                GroupAlphabet   NodeName    c_NodeId
        -------------------------------------------------------------------------------------------------------------
        Current                     12359116         Addiction Medicine         A               Nepean      4


TABLE2
        NodeId                      GroupNames      RegPerSup   FacCapacility
        ------------------------------------------------------------------------
        4                           A,B,X           1           2

My Query

select *
from table1 left join
     table2 ast
     on ast.nodeid = c_NodeId and
     GroupAlphabet in (select items from SplitString(ast.GroupNames, ','))

returns all NULLs in second table

Current                     12359116         Addiction Medicine         A               Nepean      4     NULL    NULL    NULL NULL

whereas if i try

select *
from table1 left join
     table2 ast
     on ast.nodeid = c_NodeId and
        GroupAlphabet like '%' + ast.GroupNames + '%'

the results are correct for only exact matches but not comma separated groups

SplitString is a UDF that returns a table with items datatype as nvarchar (GroupAlphabet is type varchar if it matters)

My SplitString function is

ALTER FUNCTION [dbo].[SplitString](@String NVARCHAR(max), @Delimiter char(1))    
returns @temptable TABLE (items NVARCHAR(max))       
as      
begin  
      declare @idx int   
     declare @slice NVARCHAR(max)         
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

What is your question? If it is "*Why doesn't the first query work?*" Then you will need to provide the definition of the SplitString function as well. — RBarryYoung, Aug 14 '18 at 00:44
Also, you really should not use comma-separated values in a column. It is a violation of First Normal Form and it creates no end of problems. The only valid reason to have a function like SplitString is to enable you to convert a CSV input string into a rowset for storage in appropriate relational table structures. — RBarryYoung, Aug 14 '18 at 00:50
Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. See the downvote arrow mouseover text. Please read & act on [mcve]--including a clear specification & desired output. — philipxy, Aug 14 '18 at 20:06
Possible duplicate of [join comma delimited data column](https://stackoverflow.com/questions/16507239/join-comma-delimited-data-column) — philipxy, Aug 14 '18 at 20:16
@RBarryYoung yes my question was why it isnt working, I have added the UDF. As advised by JERRY and Gordon i have normalized my data but my first query should have worked. — Samra, Aug 16 '18 at 00:05

score 3 · Answer 1 · answered Aug 14 '18 at 00:46

The proper format using like is:

select *
from table1 left join
     table2 ast
     on ast.nodeid = c_NodeId and
        ',' + ast.GroupNames + ',' like '%,' + table1.GroupAlphabet + ',%';

I'm not sure what is wrong with your first query. However, you should be using a junction table instead of storing lists of things in a delimited string. SQL has a great data structure for storing lists. It is called a table, not a string.

score 2 · Accepted Answer · answered Aug 14 '18 at 03:52

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);

sql join on comma separated column

2 Answers2