Split User Defined Function

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by kgayda » Split User Defined Function

Split User Defined Function

Rate Topic

Display Mode

Topic Options

Author

Message

kgayda

Posted Monday, October 08, 2007 2:06 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 5:10 PM
Points: 275, Visits: 168

Comments posted to this topic are about the item Split User Defined Function

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

Post #408172

wchaster

Posted Friday, February 29, 2008 2:14 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 7:43 AM
Points: 63, Visits: 109

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),
@vcDelimiter varchar(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.

BigGrin

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

Post #462684

kgayda

Posted Sunday, March 30, 2008 3:43 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 5:10 PM
Points: 275, Visits: 168

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
)
AS
BEGIN

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

RETURN
END
GO

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

Post #476732

jamiepeele

Posted Friday, January 30, 2009 7:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 20, 2011 1:32 PM
Points: 3, Visits: 7

I tried to split a string on a space character and the function blew up

select * from Split('one two', ' ')

Post #646918

jamiepeele

Posted Friday, January 30, 2009 7:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 20, 2011 1:32 PM
Points: 3, Visits: 7

some debugging and i found that when you len a blank string you get 0

//returns 0
select len(' ')

Post #646922

kgayda

Posted Friday, January 30, 2009 9:08 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 5:10 PM
Points: 275, Visits: 168

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

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

Post #647063

jamiepeele

Posted Friday, January 30, 2009 11:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 20, 2011 1:32 PM
Points: 3, Visits: 7

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.

Post #647209

kgayda

Posted Friday, January 30, 2009 1:39 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 5:10 PM
Points: 275, Visits: 168

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

Post #647310

battula.praveen

Posted Friday, June 04, 2010 12:30 PM

Grasshopper

Group: General Forum Members
Last Login: Saturday, June 12, 2010 9:31 AM
Points: 12, Visits: 14

Hello,
This is the exact solution you are looking for.
Check out: T-SQL Split UDF function by delimiter

Post #933016

« Prev Topic | Next Topic »

Permissions