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
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
Output will be as seen below
Developers can realize that different valid forms of URL addresses are considered and domain name is fetched among those different formats.