Why can't I combine two SQL Server functions?

Question

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!

Answer 1

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