How to extract the domain from a URL with MSSQL

  • Get the domain name from a URL is a string parsing exercise.
  • Normally this should be done a the front-end and not the back-end especially on a very heavy volume server.
  • SQL Server and the other modern SQL engines are very adept and fast at these string operation.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Extract domain from URL

DECLARE @url VARCHAR(35);
SET @url = 'http://www.sqlhacks.com/optimize'

SELECT @url AS 'Url',
       LEFT(SUBSTRING(@url, CHARINDEX('//',@url) + 2, 35),
       CHARINDEX('/', SUBSTRING(@url, CHARINDEX('//', @url) + 2, 35)) - 1) AS 'Domain';
go
Url                                 Domain
----------------------------------- -----------------------------------
http://www.sqlhacks.com/optimize    www.sqlhacks.com

(1 row(s) affected)
  1. I used @url as a varchar(35) to display the data properly on the screen without scrolling.
  2. I assign a very important website and web page to the @url SET @url = 'http://www.sqlhacks.com/optimize' .
  3. The left( is just in case the URL does not start at beginning.
  4. The substring(@url, charindex('//',@url) + 2, 35) is to start after the //.
  5. The charindex('/', is to stop 1 character before the the / at the end of the domain name.
  6. The second substring(@url, charindex('//', @url) + 2, 35) is to start at the beginning of the domain name.