sql server string splitting...

set nocount on
GO
if object_id('dbo.data_to_columns') is not null
drop function dbo.data_to_columns 
GO
create function dbo.data_to_columns (@input varchar(8000),@delimiter varchar(20))
     returns @Output TABLE
     (col1 varchar(8000)
     ,col2 varchar(8000)
     ,col3 varchar(8000)
     ,col4 varchar(8000)
     ,col5 varchar(8000)
     )
AS
begin
declare 
      @i       tinyint
     ,@nInput  varchar(8000) 
set @i=1 
if @input like '%'+@delimiter+'%'
begin
     while @i<=5 and @input is not null
     begin
          if @input like '%'+@delimiter+'%'
          begin
               set @nInput = left(@input,charindex(@delimiter,@input)-1)
               set @Input = right(@input,datalength(@input)-(datalength(@nInput + @delimiter)))
          end
          else
          begin
               set @nInput = @input
               set @input = null
          end 
          if @i=1
               insert into @output(col1) values(@nInput)
          else if @i=2
               update @output set col2=@ninput
          else if @i=3
               update @output set col3=@ninput
          else if @i=4
               update @output set col4=@ninput
          else if @i=5
               update @output set col5=@ninput 
          set @i=@i+1
     end
end
else
     insert into @output(col1,col2,col3,col4,col5)
     select @input,null,null,null,null 
RETURN
end
GO 
declare @t table (ident int identity,my_id varchar(30))
insert into @t (my_id) values('xxx-ttt-yys-dd-ccc')
insert into @t (my_id) values('xx-rrt-das-ff')
insert into @t (my_id) values('ff-sss-rrr')
insert into @t (my_id) values('ss-sss')
insert into @t (my_id) values('ss') 

--select ident,col1,col2,col3,col4,col5 from @t outer apply dbo.data_to_columns(my_id,'-') 
select * from @t outer apply dbo.data_to_columns(my_id,'-')

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:

select my_id
       parsename(col1,4) as col1,
       parsename(col1,3) as col2,
       parsename(col1,2) as col3,
       parsename(col1,1) as col4,
       col5
  from (select my_id,
               case when (LEN(my_id) - LEN(REPLACE(my_id, '-', ''))) > 3 
               then reverse(substring(reverse(my_id),1,charindex('-',1,reverse(my_id))-1)) 
               else null end as col5,
               replace(case when (LEN(my_id) - LEN(REPLACE(my_id, '-', ''))) > 3 
                            then reverese(substring(reverse(my_id),charindex('-',1,reverse(my_id))+1,len(my_id)))
                            else my_id end,'-',':') as col1
          from YourTable) as t1

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

-- using Rob's test data and adding a bit more variety to it...
 
create table #testvals (id int identity,somestring varchar(500));
insert #testvals (somestring) select '123-456-7890-abc';
insert #testvals (somestring) select '234-567-890-abcd';
insert #testvals (somestring) select '123';
insert #testvals (somestring) select '234-567';
insert #testvals (somestring) select '123-456-7890';
insert #testvals (somestring) select '234-567-890-abcd-efg';
insert #testvals (somestring) select '1-2-3';
insert #testvals (somestring) select '1-2-3-4-567';
insert #testvals (somestring) select '1-23-456-7890';
insert #testvals (somestring) select '1223234-567-890-abcd-efg';
insert #testvals (somestring) select '1';
insert #testvals (somestring) select null;
 
-- now using a straight select statement with the help of "magic numbers" already supplied by MS
 
select id,somestring
,case when n1.number is not NULL then SUBSTRING(somestring, 1, n1.number -1) else '' end as N1
,case when n2.number is not NULL then SUBSTRING(somestring, n1.number +1, n2.number - n1.number -1 ) else '' end as N2
,case when n3.number is not NULL then SUBSTRING(somestring, n2.number +1, n3.number - n2.number -1 ) else '' end as N3
,case when n4.number is not NULL then SUBSTRING(somestring, n3.number +1, n4.number - n3.number -1 ) else '' end as N4
,case when n5.number is not NULL then SUBSTRING(somestring, n4.number +1, n5.number - n4.number -1 ) else '' end as N5
FROM #testvals
left JOIN master..spt_values N1 with (nolock) on n1.type = 'P' and n1.number between 1 and len(somestring)+1 and n1.number = CHARINDEX('-', somestring + '-',1)
left JOIN master..spt_values N2 with (nolock) on n2.type = 'P' and n2.number between n1.number and len(somestring)+1 and n2.number = CHARINDEX('-', somestring + '-',n1.number+1)
left JOIN master..spt_values N3 with (nolock) on n3.type = 'P' and n3.number between n2.number and len(somestring)+1 and n3.number = CHARINDEX('-', somestring + '-',n2.number+1)
left JOIN master..spt_values N4 with (nolock) on n4.type = 'P' and n4.number between n3.number and len(somestring)+1 and n4.number = CHARINDEX('-', somestring + '-',n3.number+1)
left JOIN master..spt_values N5 with (nolock) on n5.type = 'P' and n5.number between n4.number and len(somestring)+1 and n5.number = CHARINDEX('-', somestring + '-',n4.number+1)
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30: