sql-server - 如何从文件夹中获取最新的文件名？

Question

我在下面使用从文件夹中获取文件列表。对于列表，我只需要获取最新的文件名。

请指教，我需要在哪里更改代码才能获取最新的文件名？

DROP TABLE #File
GO
--===== Create a holding table for the file names
 CREATE TABLE #File
        (
        FileName    SYSNAME,
        Depth       TINYINT,
        IsFile      TINYINT
        )
;
--===== Capture the names in the desired directory
     -- (Change "C:\Temp" to the directory of your choice)
 INSERT INTO #File
        (FileName, Depth, IsFile)
 EXEC xp_DirTree N'\\Backup-server\Include\Monthly\Sales\',1,1

;
--===== Find the latest file using the "constant" characters
     -- in the file name and the ISO style date.
 SELECT TOP 1 
        FileName
   FROM #File
  WHERE IsFile = 1
    AND FileName LIKE 'Sales_backup_*.bak' ESCAPE '_'
  ORDER BY FileName DESC

  select *from #file

Answer 1

所以在这种情况下，文件名包含似乎是创建日期的数字。
Sales_backup_201201312300.rar大概意思是这个文件创建于2012-01-31 23:00。
所以你所要做的就是获取名称中数字最大的文件:

 SELECT TOP 1 FileName, Depth, IsFile
 FROM #File
 WHERE IsFile = 1
 ORDER BY CAST(SUBSTRING(FileName, PATINDEX('%[0-9]%', FileName), 12) As BigInt) DESC

注意:如果您的任何文件的名称中不包含数字，此 sql 语句将引发错误。要克服这个问题，您可以这样做:

 ;WITH CTE AS
 (
     SELECT FileName, Depth, IsFile
     FROM #File
     WHERE IsFile = 1
     AND PATINDEX('%[0-9]%', FileName) > 0
 )

 SELECT TOP 1 FileName, Depth, IsFile
 FROM CTE
 ORDER BY CAST(SUBSTRING(FileName, PATINDEX('%[0-9]%', FileName), 12) As BigInt) DESC

更新:

根据您的意见，您首先需要统一文件名，然后才能进行比较。为此，我提出了这个解决方案:

;WITH CTE AS
(
    SELECT  FileName,
            Depth, 
            REPLACE( 
            CASE WHEN PATINDEX('%[0-9][_][0-9]%', FileName) = 0 THEN
                    REPLACE(FileName, '.rar', '00_0000000.rar')
            ELSE 
                FileName
            END, '_', '')
            As UnifiedFormatFileName
    FROM #File
    WHERE IsFile = 1
    AND PATINDEX('%[0-9]%', FileName) > 0
)

SELECT TOP 1 FileName, Depth
FROM CTE
ORDER BY CAST(SUBSTRING(UnifiedFormatFileName, PATINDEX('%[0-9]%', UnifiedFormatFileName), 21) As Numeric(21,0)) DESC

您可以在 rextester. 上看到它的实际效果