This task is already discussed many times. SQL Server 2005 allows to create an inline function that splits such a string. The logic of such a function is self explanatory, which also hints that SQL syntax became better:

/*
  Returns numbers table.
  Table has a following structure: table(value int not null);
  value is an integer number that contains numbers from 0 to a specified value.
*/
create function dbo.Numbers
(    
  /* Number of rows to return. */
  @count int
)
returns table
as
return
with numbers(value) as
(
  select 0
  union all
  select value * 2 + 1 from numbers where value < @count / 2
  union all
  select value * 2 + 2 from numbers where value < (@count - 1) / 2
)
select
  row_number() over(order by U.v) value
from
  numbers cross apply (select 0 v) U;

/*
  Splits string using split character.
  Returns a table that contains split positions and split values:
  table(Pos, Value)
*/
create function dbo.Split
(
  /* A string to split. */
  @value nvarchar(max),
  /* An optional split character.*/
  @splitChar nvarchar(max) = N','
)
returns table
as
return
with Bound(Pos) as
(
  select
    Value
  from
    dbo.Numbers(len(@value))
  where
    (Value = 1) or
    (substring(@value, Value - 1, len(@splitChar)) = @splitChar)
),
Word(Pos, Value) as
(
  select
    Bound.Pos,
    substring
    (
      @value,
      Bound.Pos,
      case when Splitter.Pos > 0
        then Splitter.Pos
        else len(@value) + 1
      end - Bound.Pos
    )
  from
    Bound
    cross apply
    (select charindex(@splitChar, @value, Pos) Pos) Splitter
)
select Pos, Value from Word;

Test:

declare @s nvarchar(max);

set @s = N'ALFKI,BONAP,CACTU,FRANK';

select Value from System.Split(@s, default) order by Pos;

See also: Arrays and Lists in SQL Server, Numbers table in SQL Server 2005, Parade of numbers