January 19, 2007 at 10:25 am
I went googling for a split function and found one on another forum that I liked. I copied and pasted the code over into SQL 2005 Mgmt Studio, made sure I had the right database selected, and clicked on execute. It says it excuted OK so then I went to use it in a query (select split(name) from table) and it was not found. OK??? I ran the CREATE FUNCTION code again it says it is already defined. Whenever I drill down into Programability -> Functions I cannot find the function at all. Not in tabular, scalar, aggregate, system - nowhere.
So my function has been defined but I cannot find it or use it. Where did it go?
When I had the correct database selected the code I ran was:
CREATE 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
Patrick Allmond
batteryfuel.com
For all of your laptop, camera, power tool and portable battery needs.
January 19, 2007 at 6:54 pm
You need to define the owner during the execution.
select dbo.split(name) from table
And actually since this is a Table-valued function, you need it to be in the FROM part of the SQL.
select * from dbo.split(name)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 19, 2007 at 7:23 pm
Thanks. Where can I actually find the function?
Patrick Allmond
batteryfuel.com
For all of your laptop, camera, power tool and portable battery needs.
January 19, 2007 at 8:23 pm
In SQL 2000 it's under user defined functions. In 2005 it's under Programmability > Table-valued functions.
This is all provided that your login has execute permissions on the function. If you not logged in as "sa" do so and check the permissions.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply