I am trying to split URL and get each part as domain
, category
, subcategory
etc and insert each part into a table. For example:
"www.mydomain.com/toolsanddownloads/dailymealplanner.html?languageid=6"
The purpose is to do 404 redirect if page don't exist. I am tryomg to write SQL statement usinng CTE and get each part of the domain
;with cte AS
(
SELECT
CASE
WHEN RIGHT(RTRIM(URL),1) = '/' THEN LEFT(URL,LEN(URL)-1)
WHEN RIGHT(RTRIM(URL),5) = '.html' THEN LEFT(URL,LEN(URL)-5)
ELSE URL
END AS URL1,
StartPos = CharIndex('//', URL)+2
FROM [dbo].[404RedirectTemp]
)
SELECT URL1, SUBSTRING(URL1, 8, CHARINDEX('/', URL1, 9) - 8) AS DomainName,
REVERSE(SUBSTRING(REVERSE(URL1), CHARINDEX('?', REVERSE(URL1)) + 1,
CHARINDEX('/', REVERSE(URL1)) - CHARINDEX('?', REVERSE(URL1)) -1)) AS CategoryName,
SUBSTRING(URL1, CHARINDEX('?', URL1) + 1, LEN(URL1)) AS QueryParameter
FROM cte;
I an getting always the last bit for category name and is wrong because some URL's are http://www.mydomain.com/toolsanddownloads/dailymealplanner.html?languageid=6
some
"www.mydomain.com/toolsanddownloads"
"www.mydomain.com/toolsanddownloads/dailymealplanner.html"
What i want to achieve is is no matter how many sections URL has I want to get them all as columns: domain
, categories
, subcategories
, brand
, product
If domain has only categories to get categories, if categories and subcategories to get subcategories
i have over 4000 URL in temp table which i want to loop through each one and update other table for 404 redirect
How about converting to rows and treating like an array index. For example:
Lets setup sample environment
create table #url (id int, url varchar(500));
insert into #url select 1, 'http://stackoverflow.com/questions/18660573/split-url-using-sql-and-add-to-database';
insert into #url select 2, 'www.mydomain.com/toolsanddownloads';
insert into #url select 3, 'www.mydomain.com/toolsanddownloads?test=2&b=4';
insert into #url select 4, 'www.mydomain.com/toolsanddownloads/dailymealplanner.html'
Clean the data up a bit (prob on a temp table to leave raw logs alone)
update #url set url = replace(url, 'http://','');
update #url set url = replace(url, '?','/^');
update #url set url = replace(url, '&','^');
now the fun stuff
with rslt as (
SELECT row_number() OVER( partition by id ORDER BY (SELECT 1)) depth
, value = y.i.value('.', 'nvarchar(4000)')
FROM
(
SELECT id, x = CONVERT(XML, '<i>'
+ REPLACE(url, '/', '</i><i>')
+ '</i>').query('.')
from #url
) AS a CROSS APPLY x.nodes('i') AS y(i)
)
select case
when value like '^%' then 'querystring'
when depth= 1 then 'Domain'
when depth=2 then 'categories'
when depth=3 then 'subcategories'
when depth=4 then 'brand'
when depth=5 then 'product'
end section
, case when depth>1 and charindex('.', value)>0
then left(value,charindex('.', value)-1)
else value end section
from rslt;
Results look like this:
Domain stackoverflow.com
categories questions
subcategories 18660573
brand split-url-using-sql-and-add-to-database
Domain www.mydomain.com
categories toolsanddownloads
Domain www.mydomain.com
categories toolsanddownloads
querystring ^test=2^b=4
Domain www.mydomain.com
categories toolsanddownloads
subcategories dailymealplanner
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With