Extract Parts of URL String
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Date: 24 Mar 2011
String manipulation in TSQL for anything other than the simplest task produces some very ugly looking code.
The majority of times that I need complex string manipulation, I end up building and testing from scratch, as it is usually easier than trying to understand or adapt existing code.
Fortunately, most tasks are unique.
There are, however, a few tasks that deserve to be formalized into functions. IP parsing is one, and URL parsing is another.
This table valued function is adapted from code at www.MSSQLTips.com . I stripped out the intermediate stages that were included in the original code for clarification to produce a streamlined function that returns the Base URL, Page Name and Parameter String portions of a URL
eg http: // msssqltips.com / tip.asp ? tip=1156
<base name> <page name> <parameter string>
I have included some function test calls as usual, but also the original version of TSQL containing the intermediate stages.