Having fun with PARSENAME (SQL Spackle)

Question

Post reply

Having fun with PARSENAME (SQL Spackle)

WayneS

SSC Guru

Points: 95460
More actions
March 16, 2014 at 10:30 pm

#305304

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

RichB SSCrazy Eights Points: 9651 More actions · Answer 1

RichB

SSCrazy Eights

Points: 9651

March 17, 2014 at 4:14 am

#1697582

Nicely written, thanks.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

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)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 4

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

WayneS SSC Guru Points: 95460 More actions · Answer 5

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

DEK46656 SSCrazy Points: 2111 More actions · Answer 6

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):

Mad Myche SSCommitted Points: 1593 More actions · Answer 7

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 SSC Guru Points: 95460 More actions · Answer 8

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

HappyGeek SSCoach Points: 18729 More actions · Answer 9

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

...

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 10

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

mtassin SSC-Insane Points: 23099 More actions · Answer 11

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]

thierry.vandurme Hall of Fame Points: 3340 More actions · Answer 12

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)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

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)

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 14

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.