SQL Server Remove Last 2 folders from a directory path string column

mfpqipee 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(92)

I have files written to table from both Linux and Windows

Here are the expectations: Remove the file name and the last directory if it has any numbers

'E:\OP\data\SQLSERVER\FA\09_18_2023\TEST095126.AF' Will look like this 'E:\OP\data\SQLSERVER\FA'
'E:\PTDATA\Os_040722_or.af' will look like this 'E:\PTDATA'
'E:\RXD\RXD_OR.AF' will look like this 'E:\RXD'
'/op_opps/PM/Arc/TS1X2/CLM/20190801/DYH1.AF' will look like this '/op_opps/PM/Arc/TS1X2/CLM'
'/op_opps/rxd/rxdmprd_rxdm_o.af' will look like this '/op_opps/rxd'
'/op_opps/PM/Arc/ACVRS/CLP/201905/TMOS.CMPDYH1.AF' will look like this '/op_opps/PM/Arc/ACVRS/CLP'

Here is some temp data to play with

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')

This is my starting query

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

Here is where I have gotten so far but I'm not sure how to get this logic to also apply this to windows directory's and I am not sure how to remove the last file if it has any numbers in it

I don't care so much if it has a / or \ at the end as long as the actual file name and dated path is removed

sql-server

来源：https://stackoverflow.com/questions/77230847/remove-last-2-folders-from-a-directory-path-string-column

1条答案

按热度按时间

w46czmvw1#

Here's one way, assuming SQL Server 2017 or better:

WITH c AS
(
  SELECT sep = CASE WHEN DIRECTORY LIKE '%/%' THEN '/' ELSE '\' END,
         dir = DIRECTORY
  FROM #TEMP
)
SELECT dir, newdir = CONCAT(
    CASE sep WHEN '/' THEN sep END,
    REPLACE(STRING_AGG(REVERSE(value), '/') 
            WITHIN GROUP (ORDER BY [key] DESC), '/', sep))
  FROM c
  CROSS APPLY OPENJSON
  ( 
    CONCAT('["', REPLACE(REVERSE(dir), sep, '","'), '"]')
  ) AS x
  WHERE ([key] > 1 OR ([key] = 1 AND value NOT LIKE '%[0-9]%'))
  AND value > ''
  GROUP BY dir, sep;

Example db<>fiddle

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Remove Last 2 folders from a directory path string column

1条答案

相关问题

热门标签

最新问答