Getting everything after last '/'

Question

I have a path like this in my TBL_Documents table:

Uploads/Documents/6093/12/695-Graco-SW_5-15-19.pdf

I need to compare it to a file being uploaded now that will look like this:

695-Graco-SW_5-15-19.pdf

I want to compare the path in my table with the uploaded file name. I tried using substring() on the first right / but I don't really get how substring is really working. For example, I tried to do this:

select substring(right(path,1),1,1) as path from TBL_DOCUMENT 

but it is only giving me the very first character from the right. I expected to see everything after the last / character.

How can I do this?

Answer 1

I would use an approach of finding how many characters you need to use from the right. I would do this by first reversing the string and then searching for the '/'. This will tell you how many characters from the right this '/' is. I would then use this in the RIGHT function:

SQL Fiddle

MS SQL Server 2017 Schema Setup:

Query 1:

DECLARE @documentName varchar(100) = 'Uploads/Documents/6093/12/695-Graco-SW_5-15-19.pdf'

SELECT RIGHT(@documentName, CHARINDEX('/',REVERSE(@documentName))-1)

Results:

|                          |
|--------------------------|
| 695-Graco-SW_5-15-19.pdf |

Answer 2

RIGHT(path,1) means you want [1] character from the right of the path string, or 'f'. You then wrap 'f' in a substring, asking for [1] character starting at the [1]st position of the string. Since the expression passed to substring returns 'f', your substring also returns 'f'.

You want to use a combination of charindex and reverse to handle this appropriately. SUBSTRING(path,len(path) - charindex('/',reverse(path))). That will not parse but it should get you on the right track.

In normal speak, this returns the string, starting with the right most '/' of the path, to the end of string.