SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Having fun with PARSENAME (SQL Spackle)


Having fun with PARSENAME (SQL Spackle)

Author
Message
WayneS
WayneS
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49514 Visits: 10851
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, 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

RichB
RichB
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5792 Visits: 1085
Nicely written, thanks.



SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145123 Visits: 18651
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
Learn Extended Events

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (510K reputation)SSC Guru (510K reputation)SSC Guru (510K reputation)SSC Guru (510K reputation)SSC Guru (510K reputation)SSC Guru (510K reputation)SSC Guru (510K reputation)SSC Guru (510K reputation)

Group: General Forum Members
Points: 510102 Visits: 44290
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44391 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
WayneS
WayneS
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49514 Visits: 10851
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, 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

DEK46656
DEK46656
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 589
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"
    Mad Myche
    Mad Myche
    SSC Eights!
    SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)

    Group: General Forum Members
    Points: 815 Visits: 520
    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
    WayneS
    WayneS
    SSC-Forever
    SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

    Group: General Forum Members
    Points: 49514 Visits: 10851
    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, 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

    HappyGeek
    HappyGeek
    SSCertifiable
    SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

    Group: General Forum Members
    Points: 6897 Visits: 4495
    Very good article well written and logical. Learned something new and can think of immediate use for it!! Thank you

    ...
    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