Friday, June 27, 2008

Get Filename from Filepath in T-SQL

Anatoly Lubarsky - T-SQL: Get Filename from Filepath

“…

Since there is no split function analog in T-SQL - I will use SUBSTRING, REVERSE and CHARINDEX to manipulate strings:

SELECT REVERSE(SUBSTRING(REVERSE(@fpath), 0, CHARINDEX('\', REVERSE(@fpath), 1)))
…”

I’ve reinvented this wheel a number of times and don’t think I’ve built one quite like the above…

Reversing it (no pun intended) will get you the filepath without the filename

SELECT
REVERSE
(
SUBSTRING
(
REVERSE((@fpath),
CHARINDEX('\', REVERSE((@fpath), 1) + 1,
LEN((@fpath) - CHARINDEX('\', REVERSE((@fpath), 1)
)
)

3 comments:

Michael said...

I was getting errors from REVERSE about having one parameter and I found it necessary to edit that portion:

REVERSE(SUBSTRING(REVERSE(@fpath),CHARINDEX('\', REVERSE(@fpath), 1) + 1,LEN(@fpath) - CHARINDEX('\', REVERSE(@fpath),1)))

This post is the single best method I found to accomplish splitting the path apart.

Greg said...

Nice... thanks for the update/correction.

Anonymous said...

Try RIGHT( SourcePath, CHARINDEX( '\', REVERSE( SourcePath)) - 1)