SQL Server find filename from directory column

env:Windows Server 2016

       SQL Server 2016 SP2

column裡面儲存的資料是檔案名稱與路徑，要怎麼順利把檔案名稱取出來?

1.建立存放測試資料的表

command:

CREATE TABLE #DirTree
(
subdirectory NVARCHAR(200),
depth INT,
isfile BIT
)

 

2.利用xp_dirtree放入資料到測試表

command:

INSERT INTO #DirTree (subdirectory, depth, isfile)
EXECUTE master.sys.xp_dirtree ' F:\DBBackup\dbserver03\Fullbackup\',1,1

 

3.再利用測試表的資料做出含有路徑檔名的完整測試資料

command:

SELECT 'F:\DBBackup\dbserver03\Fullbackup\' + subdirectory AS FullDirectory
INTO #DirTreeFull
FROM #DirTree

 

4.最後利用REVERSE與CHARINDEX這個function將檔名分離到一個獨立欄位

command:

SELECT *,
REVERSE(left(REVERSE(FullDirectory), CHARINDEX('\', REVERSE(FullDirectory)) - 1)) AS [FileName]
FROM #DirTreeFull

 

5.下面語法分離路徑與檔案

command:

SELECT *,
REVERSE(SUBSTRING(REVERSE(FullDirectory), CHARINDEX('\', REVERSE(FullDirectory)) + 1, LEN(FullDirectory))) AS [DirectoryName],
REVERSE(left(REVERSE(FullDirectory), CHARINDEX('\', REVERSE(FullDirectory)) - 1)) AS [FileName]
FROM #DirTreeFull