Keep end of url in SQL

Question

I have a stored procedure that gives back a string that reads like //path1/path2/item.itm, //path1/path3/item.itm or //path4/path5/item.itm what I would like to do is have it just return something like /path2/item.itm I know that there is a replace in sql along the lines of REPLACE(String, ‘//path1’, ‘’) but this wont work since the string is not consistent in all entries. I cant do this in the code because I may need to put the values back in.

If I were to do this in code (which I cant do) it would be something along the lines of

string = string.Remove(string.LastIndexOf('/'),
                       string.Length - string.LastIndexOf('/'));

but I have no idea how to do this in SQL or any idea if it can be done.

Answer 1

You can use this:

declare @str varchar(100) = '//path1/path2/item.itm'
select SUBSTRING(@str, PATINDEX('%[^/]/%', @str) + 1, LEN(@str) - PATINDEX('%[^/]/%', @str))

or, if you know max length of your strings (I assume), says max = 8000, you can use this:

select SUBSTRING(@str, PATINDEX('%[^/]/%', @str) + 1, 8000)

Answer 2

I'm not sure if this would be faster than PATINDEX() but you could reverse the string take up to the / then reverse it again something like this:

  SELECT  REVERSE(LEFT(REVERSE(path),CHARINDEX('/',REVERSE(path))))

nb, I didn't test so I might have a typo.