I want to get base url from CompleteURL using sql.
Example: completeUrl: SELECT INTO Variable in MySQL DECLARE causes syntax error?. I want get :https://stackoverflow.com/
select left(CompleteURL, charindex('/', CompleteURL, charindex('://', CompleteURL ) + 3 ) - 1 )
from urls2;
Using sql server you can use a combination of patindex and substring.
create or alter function dbo.fnGetBaseUrlFromPath(@vchURL varchar(1000), @bIncludeTrailingSlash bit)
returns varchar(1000)
as
begin
declare @BaseURL varchar(1000)
select @BaseURL =
substring(
@vchURL,
1,
case
when @vchURL like '%[A-Za-z][/?][A-Za-z]%' then patindex('%[A-Za-z][/?][A-Za-z]%',@vchURL) + case when @bIncludeTrailingSlash = 1 then 1 else 0 end
else len(@vchURL)
end
)
return @BaseURL
end
go
select dbo.fnGetBaseUrlFromPath('https://stackoverflow.com/questions/36550145/get-base-url-from-completeurl-using-sql', 1)
select dbo.fnGetBaseUrlFromPath('https://stackoverflow.com', 0)
select dbo.fnGetBaseUrlFromPath('https://stackoverflow.com/', 0)
select dbo.fnGetBaseUrlFromPath('https://stackoverflow.com?somequeryparam=123', 0)
