Split function in SQL Server 2008

Question

I have Table1 with columns like this:

+--+------+
|ID|Name  |
+--+------+
|1 |MSSQL |
+--+------+
|2 |MySQl |
+--+------+
|3 |Oracle|
+--+------+

In Table2, I have a column like

+------------+
|Databasename|
+------------+
|1,3         |
+------------+
|2           |
+------------+
|1,2         |
+------------+

My output should be:

+------------+
|Databasename|
+------------+
|MSSQL,Oracle|
+------------+
|MySQL       |
+------------+
|MSSQL,MYSQL |
+------------+

How do I get this, I need query for this..

Solution 1

You are asking for a split function but you do not have to split your values to get the result you want.

This query builds the comma separated names list in a correlated subquery using the for xml trick to concatenate values. It uses like to figure out what values to use from Table1 for each row in Table2.

select (
       select ', '+T1.Name
       from Table1 as T1
       where ','+T2.Databasename+',' like '%,'+cast(T1.ID as varchar(10))+',%'
       for xml path(''), type
       ).value('substring(text()[1], 3)', 'varchar(max)') as Databasenames
from Table2 as T2

SQL Fiddle

Solution 2

First, your best solution is to not store data in a comma-separated list in your database. You should consider fixing the table structure.

If you cannot alter the table structure, then you will need to split the data in the list to rows to assign the correct name. Once the data is split then you can concatenate the data back into the list.

There are many different split function that you can find online but here is a version that I typically use:

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;

To get your result, I would start by applying the split function and a row_number() since I do not see a unique key associated with each row. If you have a unique key on each row then you will not need the row_number():

;with cte as
(
  select rn, name, id
  from
  (
    select row_number() over(order by (select 1)) rn,
      databasename
    from table2
  ) t2
  cross apply dbo.split(t2.databasename, ',') i
  inner join table1 t1
    on i.items = t1.id
) 
select *
from cte

This query breaks your comma-separated list into the following:

| RN |   NAME | ID |
--------------------
|  1 |  MSSQL |  1 |
|  1 | Oracle |  3 |
|  2 |  MySQl |  2 |
|  3 |  MSSQL |  1 |
|  3 |  MySQl |  2 |

Once you have the data in multiple rows with the correct name, then you can use STUFF() and FOR XML PATH to concatenate it into the list. You full query would be similar to this:

;with cte as
(
  select rn, name, id
  from
  (
    select row_number() over(order by (select 1)) rn,
      databasename
    from table2
  ) t2
  cross apply dbo.split(t2.databasename, ',') i
  inner join table1 t1
    on i.items = t1.id
) 
select  
  STUFF(
         (SELECT ', ' + c2.name
          FROM cte c2
          where c1.rn = c2.rn
          order by c2.id
          FOR XML PATH (''))
          , 1, 1, '') Databasename
from cte c1
group by c1.rn
order by c1.rn;

See SQL Fiddle with Demo.

The result of the full query is:

|   DATABASENAME |
------------------
|  MSSQL, Oracle |
|          MySQl |
|   MSSQL, MySQl |

Comments

[Martin Smith]

Storing comma delimited lists in the database is not a good practice. The query for this will be pretty horrible.

[Kaf]

Search for Split Function in SQL you will get plenty.