Friday, September 27, 2013

LastIndexOf in T-SQL: Get Filename out of Path

Just a quick post for a quick solution:

I'm trying to get some information about documents stored in a SQL Server 2005 database. The documents are stored in an nText field in a table (called archive), but metadata is stored in other fields and tables.
I have the original filepath, but I wanted to get only the filename (+extenstion).

So actually, I was looking for some sort of T-SQL equivalent of the LastIndexOf C#-function. I couldn't find it easily so I thought substrings and CHARINDEX will do.. :)
Filepath is the column that contains the original filepath in the beneath code example:

RIGHT(filepath, CHARINDEX('\',REVERSE(filepath))-1)
LastIndexOf in T-SQL

When filepath = 'C:\temp\new\1\document.doc' this returns 'document.doc'
It basically:

  • reverses the column filepath
  • searches for the first '\'-character (so actually the last)
  • and returns the part to the right of the '\'.