获取SQL字符串中的父路径
获取SQL字符串中的父路径
提问于 2018-06-21 06:28:16
我有一堆用斜线分隔的路径，我需要在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/的/etc/bin/foo.txt的父级。
我如何修改它，以便为诸如/etc/bin/，即/etc/这样的目录返回正确的父目录？
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-06-21 14:47:01
也许你需要这样的东西：
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 |
+-----------------------------------------------------+Stack Overflow用户
发布于 2018-06-21 06:33:33
也许是这样的？
DECLARE @MyString NVARCHAR(MAX)
SET @MyString = 'Desktop/Subfolder/SubSubFolder/FileName.test'
SELECT LEN(@MyString) - CHARINDEX('/',REVERSE(@MyString)) AS Result在此概念的基础上扩展：
DECLARE @MyString NVARCHAR(MAX)
DECLARE @MyString2 NVARCHAR(MAX)
SET @MyString = 'Desktop/Subfolder/SubSubFolder/FileName.test'
SET @MyString2 = 'Desktop/Subfolder/SubSubFolder/'
SELECT LEFT(@MyString, LEN(@MyString) - CHARINDEX('/',REVERSE(LEFT(@MyString, LEN(@MyString) - 1)))) AS Result
SELECT LEFT(@MyString, LEN(@MyString2) - CHARINDEX('/',REVERSE(LEFT(@MyString2, LEN(@MyString2) - 1)))) AS Result页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50957929
复制相关文章
相似问题