November 29, 2004 at 1:06 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/colum
December 27, 2004 at 3:49 am
How will your function work for tokenizing this string:
server.dbo.tblName@www.microsoft.com@This.Is.A.Token.Containing.Periods
using '@' as the separator? Well, it fails miserably. Your 'parser' can't tell between an agreed-upon separator (@ in your example) and a period. Why not just cut the string using string functions, without resorting to (probably) version-dependent, poorly-functioning hacks?
December 27, 2004 at 7:14 am
I agree with Slawomir Mucha. Also, if the occurrence number provided exceeds the number of substrings, the last substring is returned. I prefer to return an empty string in those cases. Here is a function I use, which also allows for longer delimiters:
CREATE FUNCTION dbo.fGetToken
(
@parm varchar(8000),
@delim varchar(100),
@whichOccur smallint
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @occur int, @spos int, @startPos int, @maxlen int
DECLARE @token varchar(8000)
DECLARE @delimLen int
-- Len() function ignores trailing blanks, so if our delimiter is a space,
-- or multiple spaces, Len() will returns zero.
IF Len(@delim) = 0 AND DataLength(@delim) > 0
SET @delimLen = DataLength(@delim) -- DataLength(@delim) * 2 for nvarchar
ELSE
SET @delimLen = Len(@delim)
SET @occur = 0
SET @startPos = 1
SET @spos = 1 - @delimLen
SET @maxLen = Len(@parm)
WHILE @occur < @whichOccur AND @parm <> '' AND @parm IS NOT NULL AND @startPos < @maxLen
BEGIN
SET @occur = @occur + 1
SET @startPos = @spos + @delimLen
SET @spos = CHARINDEX( @delim , @parm, @startPos )
IF @spos = 0
BREAK
END
IF @occur <> @whichOccur
SET @token = '' -- or NULL
ELSE
IF @spos = 0
SET @token = Substring(@parm, @startPos, 8000)
ELSE
SET @token = SubString( @parm, @startPos, @spos - @startPos)
RETURN @token
END
December 27, 2004 at 7:31 am
I tend to agree, but let's be a little kinder when pointing out flaws. Part of what we hope to do here is spark debate.
I've used Parsename before as a hack, works well enough provided you understand the rules (splits at periods, only supports the 4 part syntax of server.db.owner.object - who thought of that?!).
December 27, 2004 at 8:49 am
I'm sorry, I didn't intend to be unkind with my remarks. I was just trying to provide an alternative that works better for me. Most code has limitations of some kind.
Happy New Year!
December 27, 2004 at 5:30 pm
Posting an alternative as you did was a great touch!
December 28, 2004 at 1:19 pm
I agree that the string must not contain a . sign
because that is not handled in the procedure
I should have mentioned it as limitation of the procedure
Eli
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy