title: SQL 2005 Split Comma Separated Column on Delimiter
body: <p>My google searches on how to split a string on a delimiter have resulted in some useful functions for splitting strings when the string is known (i.e. see below):</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER FUNCTION [dbo].[Split] (@String varchar(8000), @Delimiter char(1))     
     returns @temptable TABLE (items varchar(8000))       
     as       
     begin       
         declare @idx int       
          declare @slice varchar(8000)       
  
          select @idx = 1       
              if len(@String)&lt;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)&gt;0)  
                 insert into @temptable(Items) values(@slice)       
  
             set @String = right(@String,len(@String) - @idx)       
             if len(@String) = 0 break       
         end   
     return       
     end  
  </code></pre>
  
  <p>This works well for a known string like:</p>
  
  <pre><code>SELECT TOP 10 * FROM dbo.Split('This,Is,My,List',',')
  </code></pre>
  
  <p>However, I would like to pass a column to a function, and have it unioned together with my other data in it's own row... for example given the data:</p>
  
  <pre><code>CommaColumn   ValueColumn1   ValueColumn2
  -----------   ------------   -------------
  ABC,123       1              2
  XYZ, 789      2              3
  </code></pre>
  
  <p>I would like to write something like:</p>
  
  <pre><code>SELECT Split(CommaColumn,',') As SplitValue, ValueColumn1, ValueColumn2 FROM MyTable
  </code></pre>
  
  <p>And get back</p>
  
  <pre><code>SplitValue    ValueColumn1   ValueColumn2
  ----------    ------------   ------------
  ABC           1              2
  123           1              2
  XYZ           2              3
  789           2              3
  </code></pre>
  
  <p>Is this possible, or has anyone done this before?</p>