TSQL parsing out a URL

Question

Post reply

TSQL parsing out a URL

Digs

SSCertifiable

Points: 7333
More actions
March 19, 2011 at 12:37 pm

#237014

I track the URL referrers to my site
So I have data like
http://www.google.com/pages?=hh
http://www.google.com
http://www.google.com/vd/hhh/pages?=yy
http://www.Adomain.com/pages?=hh
http://www.Adomain.com
http://www.Adomain.com/vd/hhh/pages?=yy
I am after http://www.thedomain.com and nothing else..easy to do this in client script, how does one do this in TSQL ??

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Try this:

DECLARE @URLTable TABLE

(

URLs VARCHAR(100)

)

INSERT INTO @URLTable

SELECT 'http://www.google.com/pages?=hh'

UNION ALL SELECT 'http://www.google.com'

UNION ALL SELECT 'http://www.google.com/vd/hhh/pages?=yy'

UNION ALL SELECT 'http://www.Adomain.com/pages?=hh'

UNION ALL SELECT 'http://www.Adomain.com'

UNION ALL SELECT 'http://www.Adomain.com/vd/hhh/pages?=yy'

SELECT SUBSTRING( URLs , INDEX_SLASH , (CHARINDEX('/', URLS + '/',INDEX_SLASH) - INDEX_SLASH)) URLs

FROM

( SELECT URLS ,(CHARINDEX('/',URLS) + 2) AS INDEX_SLASH FROM @URLTable ) INDEXED

LutzM SSC Guru Points: 107049 More actions · Answer 2

Here's a slightly different approach:

SELECT SUBSTRING(URLs,8,charindex('/',URLs+'/',8)-8) AS URLs

FROM @URLTable

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

LutzM (3/19/2011)
Here's a slightly different approach:
SELECT SUBSTRING(URLs,8,charindex('/',URLs+'/',8)-8) AS URLs
FROM @URLTable

Smart Lutz!! I dint notice that the first occurance of the slash will always be at 8 🙁

Sloppy me 😛

Digs SSCertifiable Points: 7333 More actions · Answer 4

Digs

SSCertifiable

Points: 7333

March 19, 2011 at 4:26 pm

#1300973

Thanks:-)

WayneS SSC Guru Points: 95460 More actions · Answer 5

ColdCoffee (3/19/2011)
LutzM (3/19/2011)
Here's a slightly different approach:
SELECT SUBSTRING(URLs,8,charindex('/',URLs+'/',8)-8) AS URLs
FROM @URLTable
Smart Lutz!! I dint notice that the first occurance of the slash will always be at 8 🙁
Sloppy me 😛

Is there any chance of having a https? If so, do a charindex('//',URLs)+2 to get the starting position.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Digs SSCertifiable Points: 7333 More actions · Answer 6

Sorry wayne where do I put CHARINDEX('//',URLs)+2

Can you show me the full select thanks

WayneS SSC Guru Points: 95460 More actions · Answer 7

Digs (3/19/2011)
Sorry wayne where do I put CHARINDEX('//',URLs)+2
Can you show me the full select thanks

You would replace each of the "8"'s with that.

;with cte AS

(

SELECT URLs,

StartPos = CharIndex('//', URLs)+2

FROM @URLTable

)

SELECT SUBSTRING(URLs,StartPos,charindex('/',URLs+'/',StartPos)-StartPos) AS URLs

FROM cte;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

If you are doing this from a .net site you can use System.Uri server side before it hits sql to get what you need like this.

string myOutput = new Uri("http://www.sqlservercentral.com/Forums/Topic1080875-1291-1.aspx").Host;

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/