April 30, 2008 at 11:21 am
At the end of this I have the code for my split function.
I want to take a declared variable and pass it to my split function so that my result is the same as if I typed in the values manually
declare @facility char(255)
set @facility='A,B,C'
select * from fn_Split('A,B,C',',')
--this returns:
value
A
B
C
declare @facility char(255)
set @facility='A,B,C'
select * from fn_split(select @facility,',')
this returns:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ')'.
I can't figure out the syntax for the life of me.
--how to create a split function to parse a value
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION fn_Split(@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(
--position int IDENTITY PRIMARY KEY,
value varchar(8000)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
always get a backup before you try that.
April 30, 2008 at 11:43 am
select * from fn_split(select @facility,',')
Try
select * from dbo.fn_split(@facility,',')
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 30, 2008 at 11:47 am
Close but it only returns the first character in the string: A
always get a backup before you try that.
April 30, 2008 at 11:51 am
Then there's a problem/limitation with the function. I just tested it, and if you change char to varchar it will work...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 30, 2008 at 12:31 pm
ARRRGGGHHHH
You are the man. It was the use of char that was screwing it up.
thanks!
always get a backup before you try that.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply