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
|
 Updating... udf_ExtractPartsofURLString.sql (5k) Andy Hughes, Jul 18, 2012, 8:19 AM
|
