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