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?

substring(@filename, [index of last '/'] + 1, len(@filename)) To get the [index of the last '/'] -- https://stackoverflow.com/questions/1024978/find-index-of-last-occurrence-of-a-sub-string-using-t-sql — avery_larry, Sep 25 '19 at 15:18
Possible duplicate of [Is there a LastIndexOf in SQL Server?](https://stackoverflow.com/questions/39002025/is-there-a-lastindexof-in-sql-server) — Robert Kock, Sep 25 '19 at 15:19

score 2 · Accepted Answer · answered Sep 25 '19 at 15:26

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 |

score 0 · Answer 2 · answered Sep 25 '19 at 15:23

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.

Getting everything after last '/'

2 Answers2