Extract Parts of URL String

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: MSSQLTips

Date: 24 Mar 2011

Description

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.

Code

Function:

DROP FUNCTION dbo.[udf_ExtractPartsofURLString]

GO

CREATE FUNCTION [dbo].[udf_ExtractPartsofURLString]

(

@URLString VARCHAR(MAX) -- the URL string to parse

)

RETURNS @Results TABLE

(

SuppliedURLString VARCHAR(MAX), -- http://www.mssqltips.com/tip.asp?tip=1156

BaseURL VARCHAR(MAX), -- www.mssqltips.com

PageName VARCHAR(MAX), -- tip.asp

ParameterString VARCHAR(MAX) -- tip=1156

)

AS

/**********************************************************

Purpose: Parses a suppplied URL string and splits it into BaseURL,

Page Name and Parameter String components.

If the suppplied URL contains mutliple parameters, then

the paraemter string contains them all

The code is vert hard to read, with nested calls to string parsing functions, so

a summary of the arsing rules used is:

Base URL - working forward through the string, extract the text between the '//' and the last '/'

Page Name - working backwards through the string, extract the text between the '?' and the first '/'

Parameter String - working backwards through the string, extract the text before the '?'

Author: MSSQLTips

History 24 Mar 2011

18 Jul 2012 - ChillyDBA - converted to table valued function

***********************************************************/

BEGIN

INSERT @Results

(

SuppliedURLString,

BaseURL,

PageName,

ParameterString

)

SELECT

@URLString AS SuppliedURLString,

SUBSTRING(@URLString,(CHARINDEX('//',@URLString,1) + 2), CHARINDEX('/', REVERSE (@URLString), 1) ) AS BaseURL,

REVERSE(RTRIM(SUBSTRING(REVERSE (@URLString),(CHARINDEX('?', REVERSE (@URLString), 1)+1),

((CHARINDEX('/', REVERSE (@URLString), 1)) - (CHARINDEX('?', REVERSE (@URLString), 1))- 1)))) AS PageName,

REVERSE(RTRIM(SUBSTRING(REVERSE (@URLString), 1, CHARINDEX('?', REVERSE (@URLString), 1) - 1))) AS ParameterString

RETURN

END

Test Code:

SELECT * FROM [dbo].[udf_ExtractPartsofURLString]('http://www.mssqltips.com/tip.asp?tip=1156')

SELECT * FROM [dbo].[udf_ExtractPartsofURLString]('http://www.mssqltips.com/tip.asp?tip=1262')

SELECT * FROM [dbo].[udf_ExtractPartsofURLString]('http://www.tigerdirect.ca/applications/SearchTools/item-details.asp?EdpNo=2580461&sku=B69-1412&SRCCODE=CANEMBP004&cm_mmc=Email-_-Retro-_-CANEMBP004-_-viewed')

Original Code:

-- Test code containing original queries that show intermediate stages of the process

CREATE TABLE #ExampleURLs (

URL_ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,

CompleteURL VARCHAR(500))

GO