Can't Seem to Get a Split Function to Run

Question

I have a single table with a column named 'a'; here is the data:

9, 12, 27, 37

Here is my function:

USE [Testing]
GO
/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 04/13/2012 10:34:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split]
(
    @RowData nvarchar(2000),
    @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table
(
    Id int identity(1,1),
    Data nvarchar(100)
)
AS  
BEGIN
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End
    
    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END

The 2 parameters are: @RowData & @SplitOn

I'm trying to call the funtion like this:

select dbo.Split(a, ',') F
From [Testing].[dbo].[TblCSV]

Msg 4121, Level 16, State 1, Line 1
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Split", or the name is ambiguous.

I know the function and table are in the DB.  I don't know why the function doesn't work.  Worse, I can't tell why I'm getting an error named 'ambiguous' when everything is fully qualified.

Any ideas?  I know I'm going to feel silly when I see the answer.  This seems so easy, but everything I have tried has failed.

---

Ryan Shuell

Answer 1

select colist from [Testing].[dbo].[TBLCSV] t

cross apply dbo.split(t.col,',')

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 2

You have to use:

SELECT * FROM tvf

CROSS APPLY when used in conjuction with other table:

http://www.sqlusa.com/articles2005/crossapply/

The following page has delimited string split functions:

http://www.sqlusa.com/bestpractices/training/scripts/userdefinedfunction/

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 3

You can't use a table-valued function as a column.  It's telling you it can't find a function because it is looking for a function that returns a scalar value with that name, which there is not.  You need to do this:

SELECT * FROM dbo.Split(a,',');

The real issue here is that you are storing delimited data in a table!  If at all possible, split these values into rows in another table! You will have fewer grey hairs and a faster, happier database as a result.

Answer 4

Ryan,

Think about the context in which you are calling the function. If the function returns a table, what do you expect to see if you are referencing it in a column?

It is like referencing any other table, in a column list:

select OH.OrderID, (select * from dbo.[Order Details]) as c1
from dbo.Orders as OH;

Do you get it?

Try referencing the function in the FROM clause, using the APPLY operator.

select A.*, S.*
from T as A cross apply dbo.Split(A.c1, ',') as S;

Also, using a "while" loop is not the best option to accomplish this task. Check this outstanding article, from Erland Sommarskog, comparing different methods to do the shredding.

Arrays and Lists in SQL Server
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/70b39f03-d501-42f7-9557-43ae7bb09550?prof=required

---

AMB

Some guidelines for posting questions...

Answer 5

BTW, Ryan, I'm pretty sure we already discussed this exact problem before and I gave you CROSS APPLY syntax. As my friend jokes, men have very short memory, like a dog and I usually reply that dogs have pretty good memory.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

I don't have a good memory!  LOL!!  I wish I did!!

Naomi, I actually did use the Cross Apply, and it produced some kind of Cross Product - Cartesian Product (same thing).  I think there must be a way to get this to split into 1 row; not several.  If I use this:

select *
from [Testing].[dbo].[TblCSV]
CROSS APPLY dbo.Split(a, ',')

I get this:

I just thought there was a way to get everything into 1 single row.

This:

select a from [Testing].[dbo].[TBLCSV] t
cross apply dbo.split(a,',')

Gives me this:

This:

SELECT * FROM dbo.Split(a,',');

Gives this:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'a'.

Doesn't make sense...there is definitely a column named 'a'.  The most confusing thing about SLQ Server is that it gives false errors.  In the statistics world, if you get false errors, you can't rely on ANYTHING.  That's really the kiss of death!!  LOL!!

This:

select A.*
from [Testing].[dbo].[TBLCSV] as A cross apply dbo.Split(A, ',') as S;

Gives me the same as the image directly above.

---

Ryan Shuell

Answer 7

Not sure about your data, bu just add SELECT DISTINCT if you don't have a lot of data in your table. 

I suggest you look at the link Hunchback provided, may be you can change your function. 

---

Abdallah El-Chal, PMP

Answer 8

If you want to split values into individual rows and then transpose them into row knowing what you always have just 4 items, then check solutions in this thread

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c5a1006e-da14-4660-aaa7-0ed7d90a5599 and in the thread referenced in it.

Also, if you always have just 4 values in the a column, you can use parsename function to split each number into its own column.

The Split functions you find split values in the column into rows, so each individual value becomes a new row. 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 9

This:

SELECT * FROM dbo.Split(a,',');

Gives this:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'a'.

Doesn't make sense...there is definitely a column named 'a'.  The most confusing thing about SLQ Server is that it gives false errors.

It makes a lot of sense, since you are not referencing any table, in the FROM clause, which includes column [a], do you?

---

AMB

Some guidelines for posting questions...

Answer 10

Oh my!  I am getting forgetful!!

This works.

;With cte AS
(
select a, items
from [Testing].[dbo].[TblCSV]
CROSS APPLY dbo.Split(a, ','))
Select ltrim(rtrim(items))
From cte;

I used to do this day in and day out.  Then, my contract ended in March, and now, already, I find myself forgetting stuff.

---

Ryan Shuell

Answer 11

InLocoAbsentia, this was just a fun little exercise.  I'm just fiddling around with some SQL scripts today.  I'm NOT storing this in a table.  That would be absurd.  LOL!!

---

Ryan Shuell