How do I get part of URL?
-
I am sure this has been discussed before, but for the sake of time, can anyone give me a query on how to get the last two parts of URL?
For example: asdf.domain.com
I need a simple query (with no declare, no CTE, ...) to get "domain.com" only
thank you very much in advance. and for sure I will also work on this myself.
-
You know how this works.
Please post DDL and sample data.
-
Try the following type of query
SELECT PARSENAME('asdf.domain.com',2) + '.' + PARSENAME('asdf.domain.com',1) AS urlJason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
SQLRNNR (8/15/2014)
Try the following type of querySELECT PARSENAME('asdf.domain.com',2) + '.' + PARSENAME('asdf.domain.com',1) AS urlThank you so much for your quick and simple solution
-
halifaxdal (8/15/2014)
SQLRNNR (8/15/2014)
Try the following type of querySELECT PARSENAME('asdf.domain.com',2) + '.' + PARSENAME('asdf.domain.com',1) AS urlThank you so much for your quick and simple solution
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
It just turns out the solution has a deficiency: it seems cannot get result for url like more than three "."
declare @url varchar(255)set @url = 'cid-0d8636520754b1ec.users.storage.live.com'select PARSENAME(@url, 2) + '.' + PARSENAME(@url, 1)it works for this though:
declare @url varchar(255)set @url = 'cid-0d8636520754b1ec.storage.live.com'select PARSENAME(@url, 2) + '.' + PARSENAME(@url, 1) -
things getting complicated so quickly, the raw data contains also some domains that actually not domains but IP address, so I also need to bypass those records:
For records like: abc.com, abc.abc.com, abc.abc.abc.com, PARSENAME(@url, 2) + '.' + PARSENAME(@url, 1) works
For records like abc.abc.abc.abc.com, it doesn't work
For records like 10.40.1.123, it should not apply the transform
Thank you very much.
-
In the old days (pre 1998) this was simple, only handful of top level domains. Today this is quite complicated and the only way is to maintain a registry of top level domains.
You can test your logic on the following set, mind you it is simplified;-)
USE tempdb;GODECLARE @DNAME TABLE(DNAME_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,DNAME_FULL VARCHAR(100) NOT NULL);INSERT INTO @DNAME(DNAME_FULL)VALUES('www.bbc.co.uk'),('bbc.co.uk'),('part.of.bbc.co.uk'),('www.microsoft.com'),('part.of.microsoft.com'),('intel.com'),('rai.it'),('m.mbl.is');SELECT*FROM @DNAME DN -
Thanks for the discussion but I am sorry I didn't see that your reply is talking my question here:
What I am looking for is a simple query (with no declare, no CTE, ...) to get "domain.com" only, SQL RNNR replied a solution that will work for most cases but I need more sophisticated solution to address those exceptions:
1. url with more than 4 dots like: abc1.abc2.abc3.com, SQL RNNR's solution cannot pick it up
2. url with IP only: I want those be bypassed
The final query is expected as: select somemagic(@URL) as NewURL, ...., ....From sometable
Thank you again for your reply. I hope this is clearer.
-
halifaxdal (8/16/2014)
Thanks for the discussion but I am sorry I didn't see that your reply is talking my question here:What I am looking for is a simple query (with no declare, no CTE, ...) to get "domain.com" only, SQL RNNR replied a solution that will work for most cases but I need more sophisticated solution to address those exceptions:
1. url with more than 4 dots like: abc1.abc2.abc3.com, SQL RNNR's solution cannot pick it up
2. url with IP only: I want those be bypassed
The final query is expected as: select somemagic(@URL) as NewURL, ...., ....From sometable
Thank you again for your reply. I hope this is clearer.
Just trying to point out that in reality the problem isn't simple. If the requirements are strictly to retrieve the two last dotted parts then this code will work
USE tempdb;GODECLARE @DNAME TABLE(DNAME_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,DNAME_FULL VARCHAR(100) NOT NULL);INSERT INTO @DNAME(DNAME_FULL)VALUES('www.microsoft.com'),('part.of.microsoft.com'),('intel.com'),('192.168.0.1');SELECT*,CASEWHEN CHARINDEX(CHAR(46),REVERSE(DN.DNAME_FULL),CHARINDEX(CHAR(46),REVERSE(DN.DNAME_FULL),1) + 1) = 0 THEN DN.DNAME_FULLWHEN CHARINDEX(CHAR(46),REVERSE(DN.DNAME_FULL),CHARINDEX(CHAR(46),REVERSE(DN.DNAME_FULL),1) + 1) > 0 THEN RIGHT(DN.DNAME_FULL,CHARINDEX(CHAR(46),REVERSE(DN.DNAME_FULL),CHARINDEX(CHAR(46),REVERSE(DN.DNAME_FULL),1) + 1) - 1)ENDFROM @DNAME DNWHERE ISNUMERIC(REPLACE(DN.DNAME_FULL,CHAR(46),'')) = 0Results
DNAME_ID DNAME_FULL----------- ---------------------- --------------1 www.microsoft.com microsoft.com2 part.of.microsoft.com microsoft.com3 intel.com intel.comThis works only where the domain name is a two part name, domain + top-level/country code. Substantial number of domains have three parts. You stated in your question that you wanted the domain name, unless you narrow your requirements down to a two part structured domain name, my previous reply is quite relevant.
-
Eirikur Eiriksson (8/16/2014)
halifaxdal (8/16/2014)
Thanks for the discussion but I am sorry I didn't see that your reply is talking my question here:What I am looking for is a simple query (with no declare, no CTE, ...) to get "domain.com" only, SQL RNNR replied a solution that will work for most cases but I need more sophisticated solution to address those exceptions:
1. url with more than 4 dots like: abc1.abc2.abc3.com, SQL RNNR's solution cannot pick it up
2. url with IP only: I want those be bypassed
The final query is expected as: select somemagic(@URL) as NewURL, ...., ....From sometable
Thank you again for your reply. I hope this is clearer.
Just trying to point out that in reality the problem isn't simple. If the requirements are strictly to retrieve the two last dotted parts then this code will work
USE tempdb;GODECLARE @DNAME TABLE(DNAME_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,DNAME_FULL VARCHAR(100) NOT NULL);INSERT INTO @DNAME(DNAME_FULL)VALUES('www.microsoft.com'),('part.of.microsoft.com'),('intel.com'),('192.168.0.1');SELECT*,CASEWHEN CHARINDEX(CHAR(46),REVERSE(DN.DNAME_FULL),CHARINDEX(CHAR(46),REVERSE(DN.DNAME_FULL),1) + 1) = 0 THEN DN.DNAME_FULLWHEN CHARINDEX(CHAR(46),REVERSE(DN.DNAME_FULL),CHARINDEX(CHAR(46),REVERSE(DN.DNAME_FULL),1) + 1) > 0 THEN RIGHT(DN.DNAME_FULL,CHARINDEX(CHAR(46),REVERSE(DN.DNAME_FULL),CHARINDEX(CHAR(46),REVERSE(DN.DNAME_FULL),1) + 1) - 1)ENDFROM @DNAME DNWHERE ISNUMERIC(REPLACE(DN.DNAME_FULL,CHAR(46),'')) = 0Results
DNAME_ID DNAME_FULL----------- ---------------------- --------------1 www.microsoft.com microsoft.com2 part.of.microsoft.com microsoft.com3 intel.com intel.comThis works only where the domain name is a two part name, domain + top-level/country code. Substantial number of domains have three parts. You stated in your question that you wanted the domain name, unless you narrow your requirements down to a two part structured domain name, my previous reply is quite relevant.
Thank you very much for the solution and it works for my current situation.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply