SQLTeam.com | Weblogs | Forums

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Split", or the name is ambiguous


#1

create function [dbo].[Split]
(
@string nvarchar(4000),
@delimiter nvarchar(10)
)
returns @table table
(
[Value] nvarchar(4000)
)
begin
declare @nextString nvarchar(4000)
declare @pos int, @nextPos int

set @nextString = ''
set @string = @string + @delimiter

set @pos = charindex(@delimiter, @string)
set @nextPos = 1
while (@pos <> 0)
begin
    set @nextString = substring(@string, 1, @pos - 1)

    insert into @table
    (
        [Value]
    )
    values
    (
        @nextString
    )

    set @string = substring(@string, @pos + len(@delimiter), len(@string))
    set @nextPos = @pos
    set @pos = charindex(@delimiter, @string)
end
return

end

select [dbo].[Split]('Was Out, No, Position does not qualify, Fired',',')

I run this and I keep getting an error

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Split", or the name is ambiguous.


#2

It's because it's an mTVF (multi-statement Table Valued Function) and it must appear in a FROM clause as if it were a table. Like this...

select * FROM [dbo].[Split]('Was Out, No, Position does not qualify, Fired',',')

Also, because it's an mTVF, it's going to be comparatively horribly slow. Please see the following URL for a splitter that blows the doors off of splliters that contain WHILE loops and other forms of RBAR. It contains an NVARCHAR(4000) and a VARCHAR(8000) splitter in the resources section at the bottom of the article.
http://www.sqlservercentral.com/articles/Tally+Table/72993/


#3

thanks for your help!