I have a database field with a URL that looks like this:
https://sub.chem.ratio.example/report/20073997621
I need to get the end of the string after the last /
.
So I did this:
RIGHT(al.uri,charindex('/',reverse(al.uri),1)-1) AS ReportID
Which worked for most rows, producing this: 20073997621
but there are a few instances where I need to remove the word redacted
from the end of the URL like this:
https://sub.chem.ratio.example/report/20073997621/redacted
So thought I could just combine both RIGHT
and REPLACE
like this:
RIGHT(REPLACE(al.uri, '/redacted', ''),charindex('/',reverse(al.uri),1)-1) AS ReportID
But the results are not what I expect.
The rows without redacted
look good, like this:
20073997621
But if there was a redacted
in there, it also trims off some of the report ID like this:
73997621
How can I fix this?
Thanks!
Something like this, with or without the "redacted" should work:
DECLARE @url NVARCHAR(255) = 'https://sub.chem.ratio.example/report/20073997621/redacted'
SELECT REVERSE(LEFT(REVERSE(REPLACE(@url, '/redacted', '')), CHARINDEX('/',REVERSE(REPLACE(@url, '/redacted', '')))-1))