USE CMAP_Dev
GO
CREATE TABLE ExampleURLs (
URL_ID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
CompleteURL varchar(500))
GO
INSERT INTO [zCustomerDB].[dbo].[ExampleURLs] ([CompleteURL])
VALUES ('http://www.mssqltips.com/tip.asp?tip=1156')
GO
INSERT INTO [zCustomerDB].[dbo].[ExampleURLs] ([CompleteURL])
VALUES ('http://www.mssqltips.com/tip.asp?tip=1299')
GO
INSERT INTO [zCustomerDB].[dbo].[ExampleURLs] ([CompleteURL])
VALUES ('http://www.mssqltips.com/tip.asp?tip=1262')
GO
INSERT INTO [zCustomerDB].[dbo].[ExampleURLs] ([CompleteURL])
VALUES ('http://www.mssqltips.com/tip.asp?tip=1240')
GO
INSERT INTO [zCustomerDB].[dbo].[ExampleURLs] ([CompleteURL])
VALUES ('http://www.mssqltips.com/tip.asp?tip=1226')
GO


-- 2 - 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 zCustomerDB.dbo.ExampleURLs
GO 

-- 3 - File Name
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 zCustomerDB.dbo.ExampleURLs
GO

-- 4 Query Parameters
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 zCustomerDB.dbo.ExampleURLs
GO