Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Split User Defined Function Expand / Collapse
Author
Message
Posted Monday, October 8, 2007 2:06 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Friday, February 29, 2008 2:14 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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.



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!
Post #462684
Posted Sunday, March 30, 2008 3:43 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Friday, January 30, 2009 7:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, January 30, 2009 7:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, January 30, 2009 9:08 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Friday, January 30, 2009 11:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, January 30, 2009 1:39 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Friday, June 4, 2010 12:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase

Permissions Expand / Collapse