How to split a space delimited field into strings in SQL Server?

Question

I found this function that returns three rows for the following query:

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

However, I need to use the values ​​from the field instead of '1 2 3'.

I tried:

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

But this does not correspond to the wrong syntax.

He does not need to use the function above, so feel free to recommend another function or in a different way. To clarify, I only need to parse the values ​​from one row of one field.

Answer 1

split (myfield) . split , APPLY:

APPLY , .

, :

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

:

create table mytable (myfield varchar(10));
insert into mytable (myfield) values ('1 2 3');
go

create function split (@list varchar(max), @delimiter char(1))
returns @shards table (value varchar(8000))
with schemabinding
as
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 into @shards (value) 
      values (substring(@list, @i, @n-@i+1));
    set @i = @n+1;
  end
  return;
end
go

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

Answer 2

SELECT dbo.split(myfield, ' ') AS x FROM mytable

Answer 3

EXEC SP_DBCMPTLEVEL 'YOUR_DB_NAME',90;

Remus. db, 80, , <= SQL 2000. .

, wtf SQL2000 SQL2005... AHH!

MSDN , fn/usp/app: http://msdn.microsoft.com/en-us/library/bb510680.aspx

Answer 4

put UDF around your column e.g.

SELECT dbo.split(myfield, ' ')  as SplitValue
FROM mytable

Answer 5

Try

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