Select a CSV string as multiple columns

--===== If the test table exists, drop it to make reruns in SSMS eaiser
     IF OBJECT_ID('tempdb..#CSV','U') IS NOT NULL
   DROP TABLE #CSV
;
--===== Create the test table.
 CREATE TABLE #CSV --Using a Temp Table just for demo purposes
        (
         ID         INT IDENTITY(1,1) --or whatever your PK is
        ,AString    VARCHAR(8000)
        )
        ;
--===== Insert some test data
 INSERT INTO #CSV
        (AString)
 SELECT AString
   FROM (
        VALUES   ('123,456,88789,null,null')
                ,('123,456,99789,1234,null')
                ,('123,456,00789,1234,null')
                ,('1,2222,77789,null,null')
                ,('11,222,88789,null,')
                ,('111,22,99789,,')
                ,('1111,2,00789,oooo,null')
        ) v (AString)
;

--===== If the test table exists, drop it to make reruns in SSMS eaiser
     IF OBJECT_ID('tempdb..#CSV','U') IS NOT NULL
   DROP TABLE #CSV
;
--===== Create the test table.
 CREATE TABLE #CSV --Using a Temp Table just for demo purposes
        (
         ID         INT IDENTITY(1,1) --or whatever your PK is
        ,AString    VARCHAR(8000)
        )
        ;
--===== Insert some test data
 INSERT INTO #CSV
        (AString)
 SELECT AString
   FROM (
         SELECT '123,456,88789,null,null' UNION ALL
         SELECT '123,456,99789,1234,null' UNION ALL
         SELECT '123,456,00789,1234,null' UNION ALL
         SELECT '1,2222,77789,null,null'  UNION ALL
         SELECT '11,222,88789,null,'      UNION ALL
         SELECT '111,22,99789,,'          UNION ALL
         SELECT '1111,2,00789,oooo,null'
        ) v (AString)
;

CREATE FUNCTION [dbo].[DelimitedSplit8K]
/**********************************************************************************************************************
 Purpose:
 Given a string containing multiple elements separated by a single character delimiter and that single character
 delimiter, this function will split the string and return a table of the single elements (Item) and the element
 position within the string (ItemNumber). 

 Notes:
 1. Performance of this function approaches that of a CLR.
 2. Note that this code implicitly converts NVARCHAR to VARCHAR and that conversion may NOT be faithful.

 Revision History:
 Note that this code is a modification of a well proven function created as a community effort and initially documented
 at the following URL (http://www.sqlservercentral.com/articles/Tally+Table/72993/). This code is still undergoing 
 tests. Although every care has certainly been taken to ensure its accuracy, you are reminded to do your own tests to 
 ensure that this function is suitable for whatever application you might use it for.
 --Jeff Moden, 01 Sep 2013 
**********************************************************************************************************************/
--===== Define I/O parameters
        (@pString VARCHAR(8000) , @pDelimiter CHAR(1)) --DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000).
  WITH E1(N) AS (--==== Itzik Ben-Gan style of a cCTE (Cascading CTE) and
                     -- should not be confused with a much slower rCTE (Recursive CTE).
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                                      --10E+1 or 10 rows
       E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d), --10E+4 or 10,000 rows max
 cteTally(N) AS ( --=== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS ( --=== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT CASE WHEN SUBSTRING(@pString,t.N,1) = @pDelimiter COLLATE Latin1_General_BIN THEN t.N+1 END --added short circuit for casting
                   FROM cteTally t 
                  WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter COLLATE Latin1_General_BIN
                ),
cteLen(N1,L1)AS ( --=== Return start position and length (for use in substring).
                     -- The ISNULL/NULLIF combo handles the length for the 

--===== Do the split for each row and repivot to columns using a 
     -- high performance CROSS TAB. It uses the function only once
     -- for each row, which is another advantage iTVFs have over
     -- Scalare Functions.
 SELECT  csv.ID
        ,Col1 = MAX(CASE WHEN ca.ItemNumber = 1 THEN Item ELSE '' END)
        ,Col2 = MAX(CASE WHEN ca.ItemNumber = 2 THEN Item ELSE '' END)
        ,Col3 = MAX(CASE WHEN ca.ItemNumber = 3 THEN Item ELSE '' END)
        ,Col4 = MAX(CASE WHEN ca.ItemNumber = 4 THEN Item ELSE '' END)
        ,Col5 = MAX(CASE WHEN ca.ItemNumber = 5 THEN Item ELSE '' END)
   FROM #CSV csv
  CROSS APPLY dbo.DelimitedSplit8K(csv.AString,',') ca
  GROUP BY csv.ID
;
GO