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
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9914 Visits: 10573
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 1058
Nicely written, thanks.



SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32577 Visits: 18557
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

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

Group: General Forum Members
Points: 86826 Visits: 41103
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
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7363 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
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9914 Visits: 10573
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
Mr or Mrs. 500
Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)

Group: General Forum Members
Points: 546 Visits: 569
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 Veteran
    SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

    Group: General Forum Members
    Points: 229 Visits: 518
    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
    SSCrazy Eights
    SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

    Group: General Forum Members
    Points: 9914 Visits: 10573
    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
    SSCommitted
    SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

    Group: General Forum Members
    Points: 1924 Visits: 1905
    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