October 8, 2007 at 2:06 pm
Comments posted to this topic are about the item Split User Defined Function
Karen Gayda
MCP, MCSD, MCDBA
gaydaware.com
February 29, 2008 at 2:14 pm
When I ran the SELECT statement by itself I see that this is a function that exists, what is the change you are making to the default Function?
I changed the function name and tried to create it and I receive an error.
IF exists (SELECT * from dbo.sysobjects
WHERE id = object_id(N'[dbo].[SplitOnSpace]')
AND OBJECTPROPERTY(id, N'IsTableFunction') = 1)
DROP FUNCTION [dbo].[SplitOnSpace]
GO
CREATE FUNCTION dbo.SplitOnSpace (
@vcDelimitedString varchar(8000),
@vcDelimitervarchar(1)
)
GO
Msg 102, Level 15, State 1, Procedure SplitOnSpace, Line 4
Incorrect syntax near ')'.
The only change I made to your expamle was the object name.
😀
ACN is the world's largest direct seller of telecommunications and essential services.
http://helpu.acndirect.com/
ACN Digital Phone Service customers automatically enjoy unlimited calling to 60 landline
destinations around the world, including India, Mexico and the UK!
:hehe:
March 30, 2008 at 3:43 pm
Actually, you did not include the function body. You would need to add the following for it to work:
RETURNS @tblArray TABLE
(
ElementIDsmallintIDENTITY(1,1), --Array index
Elementvarchar(1000)--Array element contents
)
AS
BEGIN
DECLARE
@siIndexsmallint,
@siStartsmallint,
@siDelSizesmallint
SET @siDelSize= LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
INSERT INTO @tblArray VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
END
RETURN
END
GO
Karen Gayda
MCP, MCSD, MCDBA
gaydaware.com
January 30, 2009 at 7:14 am
I tried to split a string on a space character and the function blew up
select * from Split('one two', ' ')
January 30, 2009 at 7:16 am
some debugging and i found that when you len a blank string you get 0
//returns 0
select len(' ')
January 30, 2009 at 9:08 am
Just out of curiosity, why would you want to split on a blank string?
Karen Gayda
MCP, MCSD, MCDBA
gaydaware.com
January 30, 2009 at 11:26 am
I want to split on spaces to basically get a list of words a user may enter in a search string.
For example user may search "Blue Chevrolet Corvette"
I then want to "full text search" format "Blue Chevrolet Corvette"
into
"Blue or Chevrolet or Corvette" "Blue near Chevrolet near Corvette"
so i found your function and hooked it up then unit tested with a " " and it errored. anyway I added these lines right above your line
--Custom code added by JP to split strings on blank strings
--Todo: need futher testing on spliting with characters + ' ' 4ex split on 'abc '
if len(@vcDelimiter) < 1
begin
SET @siDelSize= 1
end
else
begin
SET @siDelSize= LEN(@vcDelimiter)
end
--End custom code
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
Although it doesn't work for all scenarios is does work for the way I want to use it. BTW, thanks for the function good stuff. and If you know of a better way to accomplish what I'm trying to do by all means let me know...thanks.
January 30, 2009 at 1:39 pm
Sorry, I misread your original post to mean zero-length string not space.
I am surprised I hadn't encountered that problem before so thanks for pointing it out. I have submitted an update that changes all the calls to LEN() to DATALENGTH(). That works fine since all of the characters are varchar and not nvarchar().
Karen Gayda
MCP, MCSD, MCDBA
gaydaware.com
June 4, 2010 at 12:30 pm
Hello,
This is the exact solution you are looking for.
Check out: T-SQL Split UDF function by delimiter
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply