SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


How to Parse a Domain Name from a URL using SQL

Sometimes programmers need to parse for domain name from a string expression which identifies the URL of a web address.
I tried to create SQL Server user-defined function which parses input url string parameter for the domain name.
On SQL Server developer can use this SQL function by running the Create Function script given below and try how the function works on different situations by running the sample select query to see the results.
I used a SQL variable data type nvarchar for keeping the url address since localized address names will probably be used on some places with an increasing atitude.
The first step in the user defined function is defining the url address string is in HTTP protocol or HTTPS protocol.
Then the sql statements controls the url address variable for "/", and "." characters and parses the url address string to fetch the domain name.

Here is the source codes of SQL function used to parse domain name from given URL address string parameter.

CREATE FUNCTION Parse_For_Domain_Name (
@url nvarchar(255)
)
returns nvarchar(255)

AS

BEGIN

declare @domain nvarchar(255)

-- Check if there is the "http://" in the @url
declare @http nvarchar(10)
declare @https nvarchar(10)
declare @protocol nvarchar(10)
set @http = 'http://'
set @https = 'https://'

declare @isHTTPS bit
set @isHTTPS = 0

select @domain = CharIndex(@http, @url)

if CharIndex(@http, @url) > 1
begin
if CharIndex(@https, @url) = 1
set @isHTTPS = 1
else
select @url = @http + @url
-- return 'Error at : ' + @url
-- select @url = substring(@url, CharIndex(@http, @url), len(@url) - CharIndex(@http, @url) + 1)
end

if CharIndex(@http, @url) = 0
if CharIndex(@https, @url) = 1
set @isHTTPS = 1
else
select @url = @http + @url

if @isHTTPS = 1
set @protocol = @https
else
set @protocol = @http

if CharIndex(@protocol, @url) = 1
begin
select @url = substring(@url, len(@protocol) + 1, len(@url)-len(@protocol))
if CharIndex('/', @url) > 0
select @url = substring(@url, 0, CharIndex('/', @url))

declare @i int
set @i = 0
while CharIndex('.', @url) > 0
begin
select @i = CharIndex('.', @url)
select @url = stuff(@url,@i,1,'/')
end
select @url = stuff(@url,@i,1,'.')

set @i = 0
while CharIndex('/', @url) > 0
begin
select @i = CharIndex('/', @url)
select @url = stuff(@url,@i,1,'.')
end

select @domain = substring(@url, @i + 1, len(@url)-@i)
end

return @domain

END
GO
Code

Here is the sample SQL Select query which SQL programmers can use to test the SQL Server user defined function Parse_For_Domain_Name which is created to parse url address to fetch the domain name.

select
dbo.Parse_For_Domain_Name('https://sql.kodyaz.com/articles/article.aspx?http://www.eralper.com'),
dbo.Parse_For_Domain_Name('http://sql.kodyaz.com/articles/article.aspx?http://www.eralper.com'),
dbo.Parse_For_Domain_Name('http://sql.kodyaz.com/'),
dbo.Parse_For_Domain_Name('/'),
dbo.Parse_For_Domain_Name('https://www.kodyaz.com/'),
dbo.Parse_For_Domain_Name('www.kodyaz.com/?http://www.eralper.com'),
dbo.Parse_For_Domain_Name('sql.kodyaz.com'),
dbo.Parse_For_Domain_Name('rs.sql.kodyaz.com'),
dbo.Parse_For_Domain_Name('kodyaz.com'),
dbo.Parse_For_Domain_Name('shttp://kodyaz.com')
go
Code

Output will be as seen below

parse URL address for domain name using SQL function

Developers can realize that different valid forms of URL addresses are considered and domain name is fetched among those different formats.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.