T-SQL - Stripping Path From FileName

If you have a field in your database that contains the fille path to a file, such as \\Server\SharePoint\Folder1\SubFolder1\SomeOtherFolder\FileName.EXT and have had the need to return just FileName.EXT should find this handy.
In the select statement, just use this:

Code:

---

RIGHT(fldFileName, CHARINDEX('\', REVERSE(fldFileName))-1)

---

Replacing fldFileName with your own field.
It works like this:
REVERSE(fldFileName))
reverses the string so that it is backwards
CHARINDEX('\', REVERSE(fldFileName))
takes that reversed string, and finds the first '\'.... wich will be the LAST '\' when normal. Take one off of that (because we don't want the '\', and use that to get the RIGHT characters of the "proper" string.

It seems kind of wierd, but it does work. Far easier than parsing it out in VB or even in SQL.

TG
:wave:

Simple, yet brilliant. Thanks, this helped tremendously.

cool tip - I made a slight change:

RIGHT('\'+fldFileName, CHARINDEX('\', REVERSE('\'+fldFileName))-1)

This way it will always work whether the '\' is in the filename or not (the original errors if it isn't)