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 ??

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  March 19, 2011 at 3:20 pm

  #1300961

  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

  March 19, 2011 at 3:36 pm

  #1300966

  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

  March 19, 2011 at 3:39 pm

  #1300967

  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

  March 19, 2011 at 4:26 pm

  #1300973

  Thanks:-)

• WayneS

  SSC Guru

  Points: 95461

  More actions

  March 19, 2011 at 7:34 pm

  #1300995

  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

  March 19, 2011 at 9:45 pm

  #1300997

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

  Can you show me the full select thanks

• WayneS

  SSC Guru

  Points: 95461

  More actions

  March 20, 2011 at 6:51 am

  #1301025

  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: 286609

  More actions

  March 21, 2011 at 2:13 pm

  #1301569

  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/