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

Having fun with PARSENAME (SQL Spackle) Expand / Collapse
Author
Message
Posted Sunday, March 16, 2014 10:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:55 PM
Points: 6,582, Visits: 8,860
Comments posted to this topic are about the item Having fun with PARSENAME (SQL Spackle)

Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1551631
Posted Monday, March 17, 2014 4:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 10, 2014 7:24 AM
Points: 1,060, Visits: 874
Nicely written, thanks.


Post #1551677
Posted Monday, March 17, 2014 6:04 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 21,209, Visits: 14,899
Well done.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1551700
Posted Monday, March 17, 2014 7:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:45 AM
Points: 36,736, Visits: 31,188
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1551728
Posted Monday, March 17, 2014 6:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:01 PM
Points: 3,609, Visits: 5,222
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1551967
Posted Tuesday, March 18, 2014 1:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:55 PM
Points: 6,582, Visits: 8,860
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1552033
Posted Tuesday, March 18, 2014 9:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 366, Visits: 431
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"
    Post #1552245
    Posted Wednesday, March 19, 2014 3:43 PM
    SSC-Enthusiastic

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

    Group: General Forum Members
    Last Login: Yesterday @ 9:25 AM
    Points: 136, Visits: 443
    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
    Post #1552822
    Posted Wednesday, March 19, 2014 6:35 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 7:55 PM
    Points: 6,582, Visits: 8,860
    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
    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, How to ask a question, Performance Problems, Common date/time routines,
    CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
    Post #1552845
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse