Extracting the Folder and File Name From a Full Path in T-SQL

To get the folder and file name from a full path, find the last backslash and cut there. Everything before it is the folder. Everything after it is the file. The tricky part is the odd inputs: bare names, empty values, trailing spaces and NULL.

Why this comes up

Someone from the storage team asks, “Which folders do our database files live in?” You query the file metadata and get full paths. Now you need the folder in one column and the file in another. Easy, until a path has no folder at all.

SQL Server has no built-in function for this. You build it from REVERSE, CHARINDEX, LEFT and RIGHT. Let me start with a small test set that includes the awkward cases.

DROP TABLE IF EXISTS #Paths;

CREATE TABLE #Paths (Id int PRIMARY KEY, FullPath nvarchar(4000));

INSERT #Paths VALUES
 (1, N'C:\Data\Sales\sales.mdf'),
 (2, N'sales.mdf'),
 (3, N'C:\Data\'),
 (4, NULL),
 (5, N'C:\Data\Q1 report.txt'),
 (6, N'sales.mdf  '),
 (7, N''),
 (8, N'/var/opt/mssql/data/sales.mdf'),
 (9, N'C:\Data\notes.txt  ');

Search from the end

CHARINDEX finds the first match, but we want the last one. The trick is to REVERSE the string first. Then the last backslash becomes the first one.

SELECT Id, FullPath,
       CHARINDEX(N'\', REVERSE(FullPath)) AS position_from_end
FROM #Paths
ORDER BY Id;

Read the position_from_end column. For path 1 it is 10: the file name sales.mdf has 9 characters, and the backslash in front of it makes 10. Paths 2, 6, 7 and 8 give 0, which means no backslash. Path 4 gives NULL.

That position tells you how much to keep on the right. The file name is the last position_from_end minus 1 characters. The folder is everything before that.

The full split

CASE handles the no-backslash branch first, so RIGHT never gets a negative length. For a bare name, the folder is empty and the whole value is the file name.

SELECT p.Id, p.FullPath,
       CASE WHEN x.Position = 0 THEN N''
            ELSE LEFT(p.FullPath, LEN(p.FullPath + N'#') - x.Position)
       END AS folder_path,
       CASE WHEN x.Position = 0 THEN p.FullPath
            ELSE RIGHT(p.FullPath, x.Position - 1)
       END AS file_name,
       DATALENGTH(CASE WHEN x.Position = 0 THEN p.FullPath
            ELSE RIGHT(p.FullPath, x.Position - 1) END) AS leaf_bytes
FROM #Paths AS p
CROSS APPLY (VALUES (CHARINDEX(N'\', REVERSE(p.FullPath)))) AS x(Position)
ORDER BY p.Id;

Look at each row. Path 1 splits into C:\Data\Sales\ and sales.mdf. Path 3 ends with a backslash, so its file name is empty. Path 4 stays NULL in every column. Path 5 keeps the space inside Q1 report.txt, since only the backslash matters.

The trailing space trap

See the odd + N'#' inside LEN? LEN ignores trailing spaces, so a path ending in spaces gets measured short. The marker character forces LEN to count them. It adds one, and the position is one more than the file name length, so the two cancel out.

Path 9 has a folder and two trailing spaces. Here is what happens without the marker.

SELECT FullPath,
       LEFT(FullPath, LEN(FullPath) - CHARINDEX(N'\', REVERSE(FullPath))) AS without_marker,
       LEFT(FullPath, LEN(FullPath + N'#') - CHARINDEX(N'\', REVERSE(FullPath))) AS with_marker
FROM #Paths
WHERE Id = 9;

The folder comes back as C:\Da without the marker, and as C:\Data\ with it. Nothing errors. The wrong answer simply looks plausible.

That is also why leaf_bytes exists. Path 6 shows 22 bytes: sales.mdf plus two trailing spaces is 11 characters at two bytes each. Your eyes cannot see those spaces, but DATALENGTH can.

Know the limits

This code understands backslashes only. Path 8 is a Linux-style path, and the output shows the problem: no folder, and the whole path as the file name. I would not quietly swap the slashes. Change separators only if you own the data and the rule is agreed.

The split is also pure string work. It does not prove the folder exists or that the SQL Server service can read it. Keep the original value next to the split in any report.

To run it on real files, replace #Paths with the physical_name column from sys.database_files. Your paths will differ from mine.

SELECT name, physical_name
FROM sys.database_files
ORDER BY file_id;

DROP TABLE IF EXISTS #Paths;

Try the same split with the paths your own server returns.

A path split is not a file check, it is just careful string work.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.