February 5, 2008 at 12:33 am
I created the following "Split" function in SQL2005>>
--------------
CREATE FUNCTION dbo.Split
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(4000)
SET @tempItemList = REPLACE (@tempItemList, ' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
--------------------
And then when i try to execute it as shown below, i get the following error:
select dbo.split('abcdef,ghi',',')
Error:
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
February 5, 2008 at 12:41 am
Hi,
The reason for this is that you have created a tabular function (meaning it returns a table).
To get data from this type of function you need to treat it as a table when calling it for example:
select * FROM dbo.split('abcdef,ghi',',')
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 5, 2008 at 12:46 am
Hi ,
Just in case you are interested.
If you have a numbers/tally table in your database which is always good to have.
Here is an alternate to your split function that does not require a loop.
CREATE FUNCTION dbo.Split
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
INSERT INTO @IDTable
SELECT SUBSTRING(@ItemList+@delimiter, N,
CHARINDEX(',', @ItemList+@delimiter, N) - N)
FROM dbo.Tally
WHERE N <= LEN(@ItemList)
AND SUBSTRING(@delimiter + @ItemList,
N, 1) = @delimiter
ORDER BY N
RETURN
END
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 5, 2008 at 12:57 am
Hi Chris,
Thank you for the assistance..:)
Problem Solved
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply