Having fun with PARSENAME (SQL Spackle)

  • Comments posted to this topic are about the item Having fun with PARSENAME (SQL Spackle)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Nicely written, thanks.

  • Well done.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Great Spackle article on an underused function, Wayne. I'm not sure why it currently only has 3 1/2 stars. I gave it a 5 because it does just exactly what a Spackle article is supposed to do and it does provide a great introduction to the alternative uses that all the heavy hitters have come to love it for. Well done.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I agree, nice Spackle article Wayne.

    It included a couple of things I didn't know about PARSENAME (because I'm probably one of the ones that underutilizes it), like it returning NULL when the name consists of more than 4 parts.

    Well done!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Rich, Jason, Jeff, and Dwain - I'm glad you liked it, and I hope you learned from it.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • I use PARSENAME all the time: it’s in my quick “design patterns” toolkit that I dip into once in a while. In addition to an IPv4 address, I’ve used it to parse a field with a mixed number of separators: for example something like “ABC/DE/F” and “MN/O”. I wrap the field in a series of nested functions (from inside out):

  • REPLACE to change “/” to “.”
  • REVERSE to change the text order. Position 4 (server name) is now at position 1 (object name): this maps the position to the left to right order of the original text.
  • PARSENAME the field
  • REVERSE the result again to get the text back in order.
  • This might seem to be overkill, but all of this uses built in T-SQL functions and runs very fast.

    Beer's Law: Absolutum obsoletum
    "if it works it's out-of-date"

  • I've used this before for parsing IP Addresses as suggested.

    Re-reading the article today made me realize that I can use it in more ways, and what took me a few hours to do in C# yesterday was replaced in a half hour today.

    The scenario I had was checking URLs to see if they matched an /x/{name}/{id} pattern and convert them to an updated pattern swapping the names and id. Using ParseName I passed it in and then did an isnumeric on element1 and if so then check the tables to see if the id existed and if so return the proper format.

    Thank you,

    Mike

    Director of Transmogrification Services
  • Mad Myche (3/19/2014)


    I've used this before for parsing IP Addresses as suggested.

    Re-reading the article today made me realize that I can use it in more ways, and what took me a few hours to do in C# yesterday was replaced in a half hour today.

    The scenario I had was checking URLs to see if they matched an /x/{name}/{id} pattern and convert them to an updated pattern swapping the names and id. Using ParseName I passed it in and then did an isnumeric on element1 and if so then check the tables to see if the id existed and if so return the proper format.

    Thank you,

    Mike

    That's great Mike. Glad you were able to make immediate use of it!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Very good article well written and logical. Learned something new and can think of immediate use for it!! Thank you

    ...

  • Nice article. Really like combining it with the REPLACE function.

  • Excellent article.

    Typically I'd just use the delimitted splitter to break the string up, and then figure out a way to put it all back together somehow.

    For smaller strings of a limitted number of delimiters I need to parse, this is pretty cool, shame it doesn't handle more than a 4 part string though... if it just parsed out strings to N delimiters, I think it would get even more use.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Nice article. May come in use one day so glad you shared that.

    Btw, I use bigint to store IP4 addresses (less space and able to use ORDER BY)

  • thierry.vandurme (1/15/2016)


    Nice article. May come in use one day so glad you shared that.

    Btw, I use bigint to store IP4 addresses (less space and able to use ORDER BY)

    It'll be interesting how you pick parts of the address out during a search.

    If you really want to save some space, separate each octet out into a separate TINYINT column. That takes only 4 bytes total, is sortable, and is searchable without having to jump through hoops.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Great article. As you illustrate with phone numbers, I use it with REPLACE in our organization to handle GL account numbers.

    Don Simpson



    I'm not sure about Heisenberg.

  • Viewing 15 posts - 1 through 15 (of 37 total)

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