Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Tokenizing a String Using PARSENAME Expand / Collapse
Author
Message
Posted Monday, November 29, 2004 1:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:49 PM
Points: 138, Visits: 270
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/colum


Post #148434
Posted Monday, December 27, 2004 3:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 28, 2005 9:11 AM
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?

Post #152503
Posted Monday, December 27, 2004 7:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 8:21 AM
Points: 977, Visits: 273

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




Post #152509
Posted Monday, December 27, 2004 7:31 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 2:35 PM
Points: 6,779, Visits: 1,868

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
Post #152511
Posted Monday, December 27, 2004 8:49 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 8:21 AM
Points: 977, Visits: 273

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!

 




Post #152536
Posted Monday, December 27, 2004 5:30 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 2:35 PM
Points: 6,779, Visits: 1,868
Posting an alternative as you did was a great touch!

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #152600
Posted Tuesday, December 28, 2004 1:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 17, 2014 2:05 AM
Points: 47, Visits: 18

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

Post #152730
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse