Tokenizing a String Using PARSENAME

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

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

  • Slawomir Mucha

    SSC Enthusiast

    Points: 167

    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

    SSChampion

    Points: 11132

    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

    SSC Guru

    Points: 119676

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

     

     

  • vadba

    SSChampion

    Points: 11132

    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

    SSC Guru

    Points: 119676

    Posting an alternative as you did was a great touch!

  • Eli Leiba

    SSC-Addicted

    Points: 461

    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

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply