SQLServerCentral / Discuss Content Posted by Leo Peysakhovich / Article Discussions / Article Discussions by Author / Tokenizing a String Using PARSENAME / Latest Posts

RE: Tokenizing a String Using PARSENAME

Eli Leiba — Tue, 28 Dec 2004 13:19:00 GMT

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

RE: Tokenizing a String Using PARSENAME

Andy Warren — Mon, 27 Dec 2004 17:30:00 GMT

Posting an alternative as you did was a great touch!

RE: Tokenizing a String Using PARSENAME

mkeast — Mon, 27 Dec 2004 08:49:00 GMT

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!

RE: Tokenizing a String Using PARSENAME

Andy Warren — Mon, 27 Dec 2004 07:31:00 GMT

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?!).

RE: Tokenizing a String Using PARSENAME

mkeast — Mon, 27 Dec 2004 07:14:00 GMT

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)ASBEGIN

DECLARE @occur int, @spos int, @startPos int, @maxlen intDECLARE @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 nvarcharELSE SET @delimLen = Len(@delim)

SET @occur = 0SET @startPos = 1SET @spos = 1 - @delimLenSET @maxLen = Len(@parm)

WHILE @occur < @whichOccur AND @parm <> '' AND @parm IS NOT NULL AND @startPos < @maxLenBEGIN SET @occur = @occur + 1 SET @startPos = @spos + @delimLen

SET @spos = CHARINDEX( @delim , @parm, @startPos )

IF @spos = 0 BREAKEND

IF @occur <> @whichOccur SET @token = '' -- or NULLELSE IF @spos = 0 SET @token = Substring(@parm, @startPos, 8000) ELSE SET @token = SubString( @parm, @startPos, @spos - @startPos)

RETURN @tokenEND

RE: Tokenizing a String Using PARSENAME

Slawomir Mucha — Mon, 27 Dec 2004 03:49:00 GMT

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?

Tokenizing a String Using PARSENAME

Leo Peysakhovich — Mon, 29 Nov 2004 13:06:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/colum