SQL Clone
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 Eights!
SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)

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



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

Group: General Forum Members
Points: 147 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
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2678 Visits: 487

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
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: Moderators
Points: 24865 Visits: 2746

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
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2678 Visits: 487

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
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: Moderators
Points: 24865 Visits: 2746
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-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 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