How to split space delimited field into rows in SQL Server? -

i found this function returns 3 rows following query:

select * dbo.split('1 2 3',' ')  

however, need use values field instead of '1 2 3'.

i tried:

select * dbo.split(select top 1 myfield mytable,' ') 

but fails saying incorrect syntax.

it doesn't have use function above, feel free recommend function or different way go it. clarify, need parse values single row of single field.

you need apply split(myfield) function each row in mytable. when split function table valued function correct answer apply operator:

the apply operator allows invoke table-valued function each row returned outer table expression of query.

so answer must be:

select * mytable  cross apply dbo.split(myfield, ' '); 

example:

create table mytable (myfield varchar(10)); insert mytable (myfield) values ('1 2 3'); go  create function split (@list varchar(max), @delimiter char(1)) returns @shards table (value varchar(8000)) schemabinding begin   declare @i int;   set @i = 0;   while @i <= len(@list)   begin     declare @n int;     set @n = charindex(@delimiter, @list, @i);     if 0 = @n     begin        set @n = len(@list);     end     insert @shards (value)        values (substring(@list, @i, @n-@i+1));     set @i = @n+1;   end   return; end go  select *  mytable   cross apply dbo.split(myfield, ' ');