Splitting up one string into three columns

I'm trying to split a hyphen-delimited string into three columns in a view. I've been using substring and len to split up the string, but it is getting very complicated (and isn't working in all cases). I've used a SPLIT function in vbscript - does t-sql have anything similar? I've attached a spreadsheet that shows what I am looking for. Maybe someone can guide me in the right direction?

Thanks.

Here you go

--drop table string
use tempdb
go
create table string (a varchar(100))
go
insert into string select 'AA'
insert into string select 'AA-AB'
insert into string select 'AA-AB-AC'
insert into string select 'AA-AB-ABC'
insert into string select 'BB'
insert into string select 'BB-BAA'
insert into string select 'BB-BAA-BC'
insert into string select 'BB-BAA-BD'
insert into string select 'BB-BAA-BD-Q'
insert into string select 'BB-BAA-BD-W'
insert into string select 'CCC'
insert into string select 'CCC-C'
insert into string select 'DD'
insert into string select 'DD-XX'
insert into string select 'EE'
go

select a,level1= case charindex('-',a,1) when 0 then a else left(a,charindex('-',a,1) -1) end,
level2=case when charindex('-',a,charindex('-',a,1)+1) =0 and charindex('-',a,1) =0 then ''
when charindex('-',a,charindex('-',a,1)+1) =0 and charindex('-',a,1) <>0 then right(a,len(a)-charindex('-',a,1) )
else substring(a,charindex('-',a,1) +1, charindex('-',a,charindex('-',a,1)+1) -charindex('-',a,1) -1) end,
level3=case when charindex('-',a,1) =0 or charindex('-',a,charindex('-',a,1)+1) =0 then ''
when charindex('-',a,charindex('-',a,1)+1) <>0 and charindex('-',a,1) <>0 and charindex('-',a,charindex('-',a,charindex('-',a,1)+1)+1 ) =0 then right(a,len(a)- charindex('-',a,charindex('-',a,1)+1) )
else substring(a,charindex('-',a,charindex('-',a,1)+1)+1 , charindex('-',a,charindex('-',a,charindex('-',a,1)+1)+1 ) -charindex('-',a,charindex('-',a,1)+1)-1 ) end
from string

create view myview as
select a,level1= case charindex('-',a,1) when 0 then a else left(a,charindex('-',a,1) -1) end,
level2=case when charindex('-',a,charindex('-',a,1)+1) =0 and charindex('-',a,1) =0 then ''
when charindex('-',a,charindex('-',a,1)+1) =0 and charindex('-',a,1) <>0 then right(a,len(a)-charindex('-',a,1) )
else substring(a,charindex('-',a,1) +1, charindex('-',a,charindex('-',a,1)+1) -charindex('-',a,1) -1) end,
level3=case when charindex('-',a,1) =0 or charindex('-',a,charindex('-',a,1)+1) =0 then ''
when charindex('-',a,charindex('-',a,1)+1) <>0 and charindex('-',a,1) <>0 and charindex('-',a,charindex('-',a,charindex('-',a,1)+1)+1 ) =0 then right(a,len(a)- charindex('-',a,charindex('-',a,1)+1) )
else substring(a,charindex('-',a,charindex('-',a,1)+1)+1 , charindex('-',a,charindex('-',a,charindex('-',a,1)+1)+1 ) -charindex('-',a,charindex('-',a,1)+1)-1 ) end
from string

Hi,

I think this is a classic requirement for a user defined function. Write a function similar to the split in vbscript. May be with an additional parameter to give u the correct element from the splitted string. For eg

udf_split(string_to_be_split,element_index)

this function can return the correct value.

Say u have a string "ABC-DEF-HIJ" and u want to get DEF. The element_index can then be 2. This way u can split a string of unlimited number of elements and retrieve the correct element to insert into ur table!

Let me know if this works
Ketan