从目录路径字符串列中删除最后 2 个文件夹

问：

我有从 Linux 和 Windows 写入表的文件

以下是期望： 删除文件名和最后一个目录（如果有任何编号）

• “E：\OP\data\SQLSERVER\FA\09_18_2023\TEST095126.AF”将如下所示“E：\OP\data\SQLSERVER\FA”
• “E：\PTDATA\Os_040722_or.af”将如下所示“E：\PTDATA”
• 'E：\RXD\RXD_OR.AF“将如下所示”E：\RXD”
• '/op_opps/PM/Arc/TS1X2/CLM/20190801/DYH1。AF“将如下所示”/op_opps/PM/Arc/TS1X2/CLM”
• '/op_opps/rxd/rxdmprd_rxdm_o.af' 将看起来像这样 '/op_opps/rxd'
• '/op_opps/PM/Arc/ACVRS/CLP/201905/TMOS.CMPDYH1。AF“将如下所示”/op_opps/PM/Arc/ACVRS/CLP”

以下是一些临时数据

CREATE TABLE #TEMP (DIRECTORY NVARCHAR(150) NOT NULL)
INSERT INTO #TEMP (DIRECTORY) VALUES ('E:\OP\data\SQLSERVER\FA\09_18_2023\TEST095126.AF')
INSERT INTO #TEMP (DIRECTORY) VALUES ('E:\ORS\ordsplprd.af')
INSERT INTO #TEMP (DIRECTORY) VALUES ('E:\PPT\PPN_ARCHIVE.af')
INSERT INTO #TEMP (DIRECTORY) VALUES ('E:\test_upgrade.af')
INSERT INTO #TEMP (DIRECTORY) VALUES ('E:\RXD\RXD_OR.AF')
INSERT INTO #TEMP (DIRECTORY) VALUES ('E:\SQLSERVER\ARO_050522_PM.AF')
INSERT INTO #TEMP (DIRECTORY) VALUES ('E:\PTDATA\Os_040722_or.af')
INSERT INTO #TEMP (DIRECTORY) VALUES ('E:\sql\Or_05May22_00011.af')
INSERT INTO #TEMP (DIRECTORY) VALUES ('/op_opps/PM/Arc/TS1X2/CLM/20190801/DYH1.AF')
INSERT INTO #TEMP (DIRECTORY) VALUES ('/op_opps/PM/Arc/TS1X2/CLM/20190801/GLT1.AF')
INSERT INTO #TEMP (DIRECTORY) VALUES ('/op_opps/PM/Arc/CRS/CLP/201706/PMHS.CAPCHP1.AF')
INSERT INTO #TEMP (DIRECTORY) VALUES ('/op_opps/PM/Arc/TS1X2/CLM/201908/RESEARC1.AF')
INSERT INTO #TEMP (DIRECTORY) VALUES ('/op_opps/PM/Arc/ACCRS/CLP/201701/PMHS.CAPCHS1.AF')
INSERT INTO #TEMP (DIRECTORY) VALUES ('/op_opps/rxd/rxdmprd_rxdm_o.af')
INSERT INTO #TEMP (DIRECTORY) VALUES ('/op_opps/PM/Arc/ACVRS/CLP/201905/TMOS.CMPDYH1.AF')
INSERT INTO #TEMP (DIRECTORY) VALUES ('/op_opps/DIR/08_10_2023/XMDYCERT145203.AF')

这是我的起始查询

SELECT LEFT(DIRECTORY,LEN(DIRECTORY)-CHARINDEX('/',REVERSE(DIRECTORY))+1)
FROM #TEMP

这是我到目前为止所取得的成就，但我不确定如何让这个逻辑也将其应用于 Windows 目录，如果最后一个文件包含任何数字，我不确定如何删除最后一个文件

我不太在乎它是否在末尾有一个 / 或 \，只要删除实际的文件名和注明日期的路径