CODEWITHSUNDEEP
sqlsql-serversql-server-2005
BrianM
BrianM

Reputation: 538

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

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.

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.

Upvotes: 4

Views: 18367

Answers (5)

wsoza
wsoza

Reputation: 33

Try 

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

Upvotes: 1

Buffalo
Buffalo

Reputation: 734

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

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294227

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, ' ');

Upvotes: 3

SQLMenace
SQLMenace

Reputation: 134961

put the UDF around your column, example

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

Upvotes: 0

Peter Lang
Peter Lang

Reputation: 55524

Have you tried

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

Upvotes: 2

Related Questions