CODEWITHSUNDEEP
sqlsql-servert-sql
user8851548
user8851548

Reputation: 177

T-SQL get sub-string up to but not including first single forward slash

Using T-SQL, I would like to retrieve the sub-string up to but not including the first single forward-slash. For example:

"https://domain/xyz/" should become "https://domain"

Would appreciate some guidance on how to accomplish this.

Upvotes: 0

Views: 57

Answers (2)

Alan Burstein
Alan Burstein

Reputation: 7918

This solution will handle one or more forward slashes after and and any protocol prefix (e.g. http, https, ftp, etc). 

declare @str varchar(100) = 'https://domain/xyz/';
select substring(@str, 1, charindex('/',@str,(charindex('://',@str))+3)-1);

If the possibility exists that there won't be a forward slash after the protocol (e.g. https://domain) then you could use the solution below. What I put together is some easily consumable sample data. 

declare @table table (s varchar(100));
insert @table values 
('https://domain1/blahblah//'),
('http://domain2/fff/ggg/hhh'),
('https://domainX/ppp/20%xx/'),
('ftp://ftpserver6/zzzzz');

select isnull(substring(s,1,nullif(charindex('/',s,(charindex('://',s))+3),0)-1),s)
from @table t;

Returns

https://domain1
http://domain2
https://domainX
ftp://ftpserver6

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270011

I believe this will work:

select left(str, patindex('%[^/]/[^/]%', str) )

If the string might end with the first hyphen, you can always do:

select left(str, patindex('%[^/]/[^/]%', str + 'x') )

Upvotes: 3

Related Questions