tsql - SQL Server 2008 T-SQL UDF Split() Tailoring -

i'm useing sql ser 2008 , have large table 1 column of data. data random string little consistency. eample: name account 445566 0010020056893010445478008 afd 369. i've been working split function stackoverflow user suggested. works great function assigns split string 1 column. need row of individual columns. present result 1col values name, account, 445566,... in result i'm looking col1 name, col2 account, col3 445566,... if provide insight on how tailor script or usage desired result appreciated.

create function [dbo].[split]  (      @string varchar(max)  ,@delimiter char  )  returns @results table  (   ordinal int  ,stringvalue varchar(max)  )   begin   set @string = isnull(@string,'')  set @delimiter = isnull(@delimiter,'')   declare   @tempstring varchar(max) = @string  ,@ordinal int = 0  ,@charindex int = 0   set @charindex = charindex(@delimiter, @tempstring)  while @charindex != 0 begin           set @ordinal += 1             insert @results values      (       @ordinal      ,substring(@tempstring, 0, @charindex)      )             set @tempstring = substring(@tempstring, @charindex + 1, len(@tempstring) - @charindex)           set @charindex = charindex(@delimiter, @tempstring)  end   if @tempstring != '' begin      set @ordinal += 1       insert @results values      (       @ordinal      ,@tempstring      )  end   return  end   --the usage: select     *      mytable m     cross apply     [dbo].[split] (m.thecolumn, ' ') s rtrim(s.stringvalue) != ''  

if know have 6 columns in string can use split functions looks , of course modify function whatever number of columns want. function can not return dynamic number of columns.

create function dbo.split6(@string varchar(max), @delimiter char(1))  returns table return (   select     substring(t.col, 1, s1.pos-1) col1,     substring(t.col, s1.pos+1, s2.pos-s1.pos-1) col2,     substring(t.col, s2.pos+1, s3.pos-s2.pos-1) col3,     substring(t.col, s3.pos+1, s4.pos-s3.pos-1) col4,     substring(t.col, s4.pos+1, s5.pos-s4.pos-1) col5,     substring(t.col, s5.pos+1, s6.pos-s5.pos-1) col6   (select @string+replicate(@delimiter, 6)) t(col)     cross apply (select charindex(@delimiter, t.col, 1)) s1(pos)     cross apply (select charindex(@delimiter, t.col, s1.pos+1)) s2(pos)     cross apply (select charindex(@delimiter, t.col, s2.pos+1)) s3(pos)     cross apply (select charindex(@delimiter, t.col, s3.pos+1)) s4(pos)     cross apply (select charindex(@delimiter, t.col, s4.pos+1)) s5(pos)     cross apply (select charindex(@delimiter, t.col, s5.pos+1)) s6(pos) ) 

test:

declare @t table (col varchar(100))  insert @t values  ('name account 445566 0010020056893010445478008 afd 369'),  (''),  ('1 2'),  ('1  3')  select s.col1, s.col2, s.col3, s.col4, s.col5, s.col6 @t t   cross apply     dbo.split6(t.col, ' ') s 

result:

col1  col2     col3    col4                       col5  col6 ----  -------  ------  -------------------------  ----  ---- name  account  445566  0010020056893010445478008  afd   369  1     2              1              3