Cool Tips‎ > ‎String Manipulation‎ > ‎

String Manipulation - 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
INSERT INTO #ExampleURLs ([CompleteURL])
VALUES ('http://www.mssqltips.com/tip.asp?tip=1156')
GO
INSERT INTO #ExampleURLs ([CompleteURL])
VALUES ('http://www.mssqltips.com/tip.asp?tip=1299')
GO
INSERT INTO #ExampleURLs ([CompleteURL])
VALUES ('http://www.mssqltips.com/tip.asp?tip=1262')
GO
INSERT INTO #ExampleURLs ([CompleteURL])
VALUES ('http://www.mssqltips.com/tip.asp?tip=1240')
GO
INSERT INTO #ExampleURLs ([CompleteURL])
VALUES ('http://www.mssqltips.com/tip.asp?tip=1226')
GO
INSERT INTO #ExampleURLs ([CompleteURL])
VALUES ('http://www.tigerdirect.ca/applications/SearchTools/item-details.asp?EdpNo=2580461&sku=B69-1412&SRCCODE=CANEMBP004&cm_mmc=Email-_-Retro-_-CANEMBP004-_-viewed')
GO


-- get base URL
SELECT URL_ID,
CompleteURL, -- URL
(CHARINDEX('//', CompleteURL, 1) + 1), -- Position of the double slashes
CHARINDEX('/', REVERSE (CompleteURL), 1), -- Position of the last single slash
SUBSTRING(CompleteURL,
(
CHARINDEX('//', CompleteURL, 1) + 2),
CHARINDEX('/', REVERSE (CompleteURL), 1) ) -- Final string
FROM #ExampleURLs


-- get page name from url
SELECT URL_ID,
--REVERSE (CompleteURL), -- Backwards version of the URL
--CHARINDEX('/', REVERSE (CompleteURL), 1), -- Position of the slash
--CHARINDEX('?', REVERSE (CompleteURL), 1), -- Position of the question mark
--((CHARINDEX('/', REVERSE (CompleteURL), 1)) - (CHARINDEX('?', REVERSE (CompleteURL), 1))-1), -- Length
REVERSE(RTRIM(SUBSTRING(REVERSE (CompleteURL),
(
CHARINDEX('?', REVERSE (CompleteURL), 1)+1),
((
CHARINDEX('/', REVERSE (CompleteURL), 1)) - (CHARINDEX('?', REVERSE (CompleteURL), 1))- 1)))) -- Final parsed value
FROM #ExampleURLs

-- get parameters from url
SELECT URL_ID,
REVERSE (CompleteURL), -- Backwards version of the URL
CHARINDEX('?', REVERSE (CompleteURL), 1), -- Position of the question mark
REVERSE(RTRIM(SUBSTRING(REVERSE (CompleteURL), 1, CHARINDEX('?', REVERSE (CompleteURL), 1) - 1))) -- Final parsed value
FROM #ExampleURLs

DROP TABLE #ExampleURLs


ċ
udf_ExtractPartsofURLString.sql
(5k)
Andy Hughes,
Jul 18, 2012, 8:19 AM
Comments