Return a substring from a specified string in SQL Server

Question

I've below query:

DECLARE @url varchar (max)='http://v.mercola.com/blogs/public_blog/New-Diet-Pill-Expands-1-000-Times-in-Your-Stomach-24728.aspx' 
SELECT replace(replace(RIGHT(@URL , CHARINDEX ('/' ,REVERSE(@URL))-1),'.aspx',''),'-',' ') as abc

Which returns below output:

Actual output -

Expected output

i.e i want to eliminate the string after last occurrence of -.

What changes do i have to make to get the expected output.. In all i want a substring after last occurence of / and before last occurence of - as shown above.

Please help and thanks in advance...!

Answer 1

Try this

DECLARE @url VARCHAR (max)='http://v.mercola.com/blogs/public_blog/New-Diet-Pill-Expands-1-000-Times-in-Your-Stomach-24728.aspx'

SELECT Reverse(LEFT(mid, Charindex('/', mid) - 1))
FROM   (SELECT Substring(Reverse(@url), Charindex('-', Reverse(@url)) + 1, Len(@url)) AS mid) a