Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tokenizing a String Using PARSENAME


Tokenizing a String Using PARSENAME

Author
Message
Leo Peysakhovich
Leo Peysakhovich
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 339
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/colum



Slawomir Mucha
Slawomir Mucha
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 1

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?


vadba
vadba
SSC Eights!
SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)

Group: General Forum Members
Points: 993 Visits: 406

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





Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7183 Visits: 2679

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

 

 



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
vadba
vadba
SSC Eights!
SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)SSC Eights! (993 reputation)

Group: General Forum Members
Points: 993 Visits: 406

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!

 





Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7183 Visits: 2679
Posting an alternative as you did was a great touch!

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Eli Leiba
Eli Leiba
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 19

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search