Hall of Fame
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/colum
How will your function work for tokenizing this string:
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?
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
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
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
SET @occur = @occur + 1
SET @startPos = @spos + @delimLen
SET @spos = CHARINDEX( @delim , @parm, @startPos )
IF @spos = 0
IF @occur <> @whichOccur
SET @token = '' -- or NULL
IF @spos = 0
SET @token = Substring(@parm, @startPos, 8000)
SET @token = SubString( @parm, @startPos, @spos - @startPos)
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?!).
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!
Posting an alternative as you did was a great touch!
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
Viewing 7 posts - 1 through 6 (of 6 total)