In a column named "server_url" there are links :
After .com there is always a "/" present.
I want to get only the domain name as the output : www.abc.com , www.abcd.com
Have to remove the http:// from the start and whatever is there after the third "\".
Tried :
SUBSTRING( server_url, (charindex(':',server_url)+3), (charindex('/',server_url,10)))
I get the http removed. But its not removing the part after third '/'.
Please suggest a function to do the same.
the third parameter is length, so you need to remove the length of the ignored portion at the start, so something like (beware of fence posts):
SUBSTRING( server_url, (charindex(':',server_url)+3), (charindex('/',server_url,10) - (charindex(':',server_url)+3)))
Assuming SQL Server, this seems to work:
declare @t table (server_url varchar(max) not null)
insert into @t(server_url) values
('http://www.abc.com/xyz/cv'),
('https://www.abc.com/lmn/rq'),
('https://www.abcd.com/kl')
;With Positions as (
select server_url,
CASE WHEN SUBSTRING(server_url,5,1)='s' THEN 8 ELSE 7 END as StartPosition,
CHARINDEX('/',server_url,9) as EndPosition
from @t
)
select SUBSTRING(server_url,StartPosition,EndPosition-StartPosition)
from Positions
I could do it without the Common Table Expression by repeating the StartPosition CASE expression multiple times, but I felt that this was cleaner.
SELECT CONCAT(
SUBSTRING_INDEX(
SUBSTRING_INDEX(server_url', '.com/', 1), '//', -1
), '.com'
)
