CODEWITHSUNDEEP
sqlsql-serversubstring
Parth mehta
Parth mehta

Reputation: 1446

Substring in MS SQL to get the required o/p

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.

Upvotes: 0

Views: 126

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

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.

Upvotes: 1

Rowland Shaw
Rowland Shaw

Reputation: 38128

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)))

Upvotes: 2

Alexander
Alexander

Reputation: 3179

SELECT CONCAT(
          SUBSTRING_INDEX(
             SUBSTRING_INDEX(server_url', '.com/', 1), '//', -1
                          ), '.com'
              )

Upvotes: 0

Related Questions