SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by kgayda / Split User Defined Function / Latest Posts

RE: Split User Defined Function

battula.praveen — Fri, 04 Jun 2010 12:30:32 GMT

Hello,This is the exact solution you are looking for.Check out: [url=http://praveenbattula.blogspot.com/2010/06/best-split-udf-function-with-delimeter.html]T-SQL Split UDF function by delimiter[/url]

RE: Split User Defined Function

kgayda — Fri, 30 Jan 2009 13:39:04 GMT

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().

RE: Split User Defined Function

jamiepeele — Fri, 30 Jan 2009 11:26:07 GMT

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) > 0Although 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.

RE: Split User Defined Function

kgayda — Fri, 30 Jan 2009 09:08:20 GMT

Just out of curiosity, why would you want to split on a blank string?

RE: Split User Defined Function

jamiepeele — Fri, 30 Jan 2009 07:16:59 GMT

some debugging and i found that when you len a blank string you get 0//returns 0select len(' ')

RE: Split User Defined Function

jamiepeele — Fri, 30 Jan 2009 07:14:04 GMT

I tried to split a string on a space character and the function blew upselect * from Split('one two', ' ')

RE: Split User Defined Function

kgayda — Sun, 30 Mar 2008 15:43:50 GMT

Actually, you did not include the function body. You would need to add the following for it to work:RETURNS @tblArray TABLE ( ElementID smallint IDENTITY(1,1), --Array index Element varchar(1000) --Array element contents )ASBEGIN DECLARE @siIndex smallint, @siStart smallint, @siDelSize smallint 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 RETURNENDGO

RE: Split User Defined Function

wchaster — Fri, 29 Feb 2008 14:14:53 GMT

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]GOCREATE FUNCTION dbo.SplitOnSpace ( @vcDelimitedString varchar(8000), @vcDelimiter varchar(1))GOMsg 102, Level 15, State 1, Procedure SplitOnSpace, Line 4Incorrect syntax near ')'.The only change I made to your expamle was the object name.

Split User Defined Function

kgayda — Mon, 08 Oct 2007 14:06:53 GMT

Comments posted to this topic are about the item [B]Split User Defined Function[/B]