sql - 获取 SQL 字符串中的父路径

Question

我有一堆用斜杠分隔的路径，我需要在 SQL 中找到它们的父级例如/etc/bin/ 的父级将是 /etc/。 (目录类型为0)

/etc/bin/foo.txt 的父级将是 /etc/bin/ 等等。 (文件类型为 1)

我有以下代码，可以正确解析字符串并在字符串是文件时找到父级。

DECLARE @pathsToModify TABLE( path NVARCHAR(MAX), type INT )
INSERT INTO @pathsToModify VALUES('/etc/bin/', 0)
INSERT INTO @pathsToModify VALUES ('/etc/bin/foo.txt', 1)

WHILE EXISTS(SELECT * FROM @pathsToModify)
BEGIN
    DECLARE @path NVARCHAR(MAX)
    SELECT TOP 1 @path = path FROM @pathsToModify
    Declare @products Nvarchar(MAX) = ''
    SET @products = @path
    Declare @individual Nvarchar(MAX) = null
    DECLARE @finalPath NVARCHAR(MAX) = ''

    WHILE LEN(@products) > 0
    BEGIN
        IF PATINDEX('%/%', @products) > 0
        BEGIN
            SET @individual = SUBSTRING(@products, 0, PATINDEX('%/%', @products))
            SET @finalPath = @finalPath + @individual +  '/'

            IF (PATINDEX('%.%',@individual) > 0 OR @products = @individual)
            BEGIN
                RETURN;
            END

            SET @products = SUBSTRING(@products,
                              LEN(@individual + '/') + 1,
                              LEN(@products))
        END
        ELSE
        BEGIN
            SET @individual = @products
            SET @products = NULL
        END
    END

SELECT @finalPath

DELETE FROM @pathsToModify WHERE path = @path
END 

上面的代码正确地将 /etc/bin/foo.txt 的父级查找为 /etc/bin/。

如何修改此设置，以便为 /etc/bin/ 等目录返回正确的父目录，即 /etc/ ？

Answer 1

也许你需要这样的东西:

DECLARE @path VARCHAR(MAX)

SET @path = '/etc/bin/sub/foo.txt'
-- Check if the first character is /, if true, then remove it from the path.
SET @path = CASE WHEN CHARINDEX('/', @path) = 1 THEN RIGHT(@path, LEN(@path) - 1) ELSE @path END 

DECLARE 
    @Root       VARCHAR(MAX),
    @fileName   VARCHAR(MAX), 
    @fileRoot   VARCHAR(MAX)

SELECT 
    @Root       = LEFT(@path, CHARINDEX('/', @path) - 1) 
,   @fileName   = RIGHT(@path, CHARINDEX('/', REVERSE(@path)) - 1)
,   @fileRoot   = LEFT(@path, LEN(@path) - LEN(RIGHT(@path, CHARINDEX('/', REVERSE(@path)) - 1)) - 1)




SELECT 
    ROOT                = LEFT(@path, LEN(@Root)) 
,   FileDirectoryRoot   = LEFT(@path, LEN(@path) - (CHARINDEX('/', REVERSE(@fileRoot)) + LEN(@fileName) + 1)    )
,   FileDirectory       = LEFT(@path, LEN(@fileRoot))
,   FileName            = RIGHT(@path, LEN(@fileName)) 

输出:

+------+-------------------+---------------+----------+
| ROOT | FileDirectoryRoot | FileDirectory | FileName |
+-----------------------------------------------------+
| etc  | etc/bin           | etc/bin/sub   | foo.txt  |
+-----------------------------------------------------+