Split URL using SQL and add to database

Question

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

Have you considered doing the preprocessing in some other language? This just screams edge cases. — SubSevn, Sep 06 '13 at 14:51
Alternatively, you can have all the links in a text editor, Search & Replace `/` with `,` and save as CSV. Now each item is in a respective column ready for you to use however you want. Also, if links are never bigger then 4 parts, you might try [`PARSENAME`](http://stackoverflow.com/a/2685/1618257) — David Starkey, Sep 06 '13 at 15:07
It needs to be from the database as all links are added dynamical. I need to create stored procedure to check all links lets say once a week and update the corresponding tables — user2693802, Sep 08 '13 at 20:01

score 4 · Answer 1 · answered Sep 11 '13 at 02:08

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

Split URL using SQL and add to database

1 Answers1