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

Question

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

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

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

I tried:

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

But it fails saying incorrect syntax.

Ideas on how to do this? It doesn't have to use the function above, so feel free to recommend another function or different way to go about it. To clarify, I only need to parse the values from a single row of a single field.

Answer 1

Have you tried

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

Answer 2

put the UDF around your column, example

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

Answer 3

You need to apply the split(myfield) function to each row in mytable. When the split function is a table valued function the correct answer is the APPLY operator:

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.

So the answer must be:

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

Example:

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 4

EXEC SP_DBCMPTLEVEL 'YOUR_DB_NAME',90;

Should fix the problem of Remus's incompatible code. I just looked into my own db and it was set to level '80' which means it supports <= SQL 2000. After applying the procedure above, the code runs and works perfectly.

Now I just need to find out wtf relies on SQL2000 and breaks in SQL2005...AHH!

This MSDN link will help you determine whether your fn/usp/app layers will be negatively impacted: http://msdn.microsoft.com/en-us/library/bb510680.aspx