Parse file name and path from full path

Question

I need to parse file name and file path from full path using SQL Query.

Eg. Fullpath - \SERVER\D$\EXPORTFILES\EXPORT001.csv

FileName        Path
EXPORT001.csv   \\SERVER\D$\EXPORTFILES\

AgentSQL · Accepted Answer · 2016-11-08T18:50:39.687

74

Use this -

DECLARE @full_path VARCHAR(1000)
SET @full_path = '\\SERVER\D$\EXPORTFILES\EXPORT001.csv'

SELECT LEFT(@full_path,LEN(@full_path) - charindex('\',reverse(@full_path),1) + 1) [path], 
       RIGHT(@full_path, CHARINDEX('\', REVERSE(@full_path)) -1)  [file_name]

edited Nov 08 '16 at 18:50

answered Oct 21 '13 at 22:26

AgentSQL

2,480
15
21

27

Why not just: RIGHT(YOUR_PATH, CHARINDEX('\', REVERSE(YOUR_PATH)) -1) – Stefan Steiger Dec 11 '14 at 08:10
I have tested it and it seems sufficient. The only disadvantage is that this works only if backslash if available in constrast to Path.GetFileName(path) in .NET – Tomas Kubes Nov 29 '15 at 12:57
3

@StefanSteiger He did... he provided 2 fields instead of 1 – Joe Phillips Jan 19 '17 at 22:30

score 21 · Answer 2 · edited May 23 '17 at 12:32

I do a lot of ETL work and I was looking for a function that I could use and qub1n's solution works very good except for values without a back slash. Here is a little tweak of qub1n's solution that will handle strings without back slashes:

Create FUNCTION fnGetFileName
(
    @fullpath nvarchar(260)
) 
RETURNS nvarchar(260)
AS
BEGIN
    IF(CHARINDEX('\', @fullpath) > 0)
       SELECT @fullpath = RIGHT(@fullpath, CHARINDEX('\', REVERSE(@fullpath)) -1)
       RETURN @fullpath
END

Samples:

    SELECT [dbo].[fnGetFileName]('C:\Test\New Text Document.txt') --> New Text Document.txt
    SELECT [dbo].[fnGetFileName]('C:\Test\Text Docs\New Text Document.txt') --> New Text Document.txt
    SELECT [dbo].[fnGetFileName]('New Text Document.txt') --> New Text Document.txt
    SELECT [dbo].[fnGetFileName]('\SERVER\D$\EXPORTFILES\EXPORT001.csv') --> EXPORT001.csv

Here is a LINK to SqlFiddle

This solution works even if a the field doesn't contain a valid path (not backslash, for example). — Sylvain Rodrigue, Jun 06 '18 at 09:06

score 5 · Answer 3 · answered Apr 02 '14 at 10:40

Here is the simplest way

DECLARE @full_path VARCHAR(1000)
SET @full_path = '\\SERVER\D$\EXPORTFILES\EXPORT001.csv'
SELECT  LEFT(@full_path, LEN(@full_path) - CHARINDEX('\', REVERSE(@full_path)) - 1),
        RIGHT(@full_path, CHARINDEX('\', REVERSE(@full_path)) - 1)

Tomas Kubes · Answer 4 · 2015-12-09T19:25:46.600

Answer based on comment by Stefan Steiger:

Create FUNCTION GetFileName
(
 @fullpath nvarchar(260)
) 
RETURNS nvarchar(260)
AS
BEGIN
DECLARE @charIndexResult int
SET @charIndexResult = CHARINDEX('\', REVERSE(@fullpath))

IF @charIndexResult = 0
    RETURN NULL 

RETURN RIGHT(@fullpath, @charIndexResult -1)
END
GO

Test code:

DECLARE @fn nvarchar(260)

EXEC @fn = dbo.GetFileName 'AppData\goto\image.jpg'
PRINT @fn -- prints image.jpg

EXEC @fn = dbo.GetFileName 'c:\AppData\goto\image.jpg'
PRINT @fn -- prints image.jpg

EXEC @fn = dbo.GetFileName 'image.jpg'
PRINT @fn -- prints NULL

score 2 · Answer 5 · answered Oct 17 '15 at 21:35

Here's a link where someone made several functions related to this need:

CREATE FUNCTION [dbo].[GetFileName]
CREATE FUNCTION [dbo].[GetFileNameWithoutExtension]
CREATE FUNCTION [dbo].[GetDirectoryPath]
CREATE FUNCTION [dbo].[GetExtension]

http://www.codeproject.com/Tips/866934/Extracting-the-filename-from-a-full-path-in-SQL-Se?msg=5145303#xx5145303xx

score 2 · Answer 6 · edited Apr 20 '16 at 10:10

2

Using REVERSE is easier to see

DECLARE @full_path VARCHAR(1000)
SET @full_path = '\\SERVER\D$\EXPORTFILES\EXPORT001.csv'

select REVERSE(LEFT(REVERSE(@full_path),CHARINDEX( '\',REVERSE(@full_path))-1)) as [FileName],
       replace(@full_path, REVERSE(LEFT(REVERSE(@full_path),CHARINDEX( '\',REVERSE(@full_path))-1)),'') as [FilePath]

edited Apr 20 '16 at 10:10

Aleksej

21,858
5
28
36

answered Apr 19 '16 at 18:47

user6226611

21
1

score 2 · Answer 7 · edited Apr 05 '19 at 11:20

2

How about:

reverse(LEFT(REVERSE(FileName), Coalesce(nullif(CHARINDEX('\', REVERSE(FileName))-1, -1), len(FileName)) ))

Weird, I know, but it means I can avoid the no \ issue and still do it inline.

edited Apr 05 '19 at 11:20

Reema Parakh

1,147
1
15
39

answered Apr 05 '19 at 10:56

LJ Hogan

21
1

score 1 · Answer 8 · edited Dec 08 '15 at 17:32

Declare @filepath Nvarchar(1000)
Set @filepath = 'D:\ABCD\HIJK\MYFILE.TXT'

    --Using Left and Right
    Select LEFT(@filepath,LEN(@filePath)-CHARINDEX('\',REVERSE(@filepath))+1) Path,
        RIGHT(@filepath,CHARINDEX('\',REVERSE(@filepath))-1) FileName

    -- Using Substring      
    Select SUBSTRING(@filepath,1,LEN(@filepath)-CHARINDEX('\',REVERSE(@filepath))+1) Path,
        REVERSE(SUBSTRING(REVERSE(@filepath),1,CHARINDEX('\',REVERSE(@filepath))-1)) FileName

score 0 · Answer 9 · answered Dec 25 '17 at 06:41

0

select 
LTRIM(
RTRIM(
REVERSE(
SUBSTRING(
REVERSE(Filename),0,CHARINDEX('\',REVERSE(Filename),0))
)))
 from TblFilePath

answered Dec 25 '17 at 06:41

Vijay Badgujar

1
1

score 0 · Answer 10 · answered May 30 '19 at 00:07

For anyone that wants to perform this operation and also trim off the file extension:

SELECT
    LEFT(
        RIGHT(<FIELD>, CHARINDEX('\', REVERSE(<FIELD>)) - 1),
        LEN(RIGHT(<FIELD>, CHARINDEX('\', REVERSE(<FIELD>)) - 1)) -
          CHARINDEX('.', REVERSE(<FIELD>))
    )
FROM <TABLE>

Note that this doesn't allow for when there are no slashes - it will need modification if this is the case

Parse file name and path from full path

10 Answers10

Linked