我想從具有許多相同分隔符的值中多次提取字串的一部分，并將提取的字串放入新列中。
這是我正在處理的資料示例（這些檔案夾路徑可能更長，具體取決于檔案夾的深度）：
檔案夾路徑：
Q:\downloads\extraction\bee\honey\
Q:\desktop\chocolate\london\time\spring\
Q:\documents\cars\astonmartin\vanquish\
我想從左側提取分隔符之間的整個字串，并將它們放入字串不同階段的新列中，例如
| 檔案夾路徑 | 根目錄 | 子目錄 | 子目錄 |
|---|---|---|---|
| Q:\documents\cars\astonmartin\vanquish\ | 問：\檔案 | 問：\檔案\汽車 | Q:\documents\cars\astonmartin |
我只是想知道這是否可行，尤其是當檔案夾名稱具有不同長度的所有型別時。
任何幫助都會讓我非常感激 - 謝謝！
uj5u.com熱心網友回復：
更新（使用第二個解決方案）
解決方案 #1 - Charindex 級聯應用：
--==== Sample Data
DECLARE @folders TABLE (Folder VARCHAR(1000));
INSERT @folders VALUES
('Q:\downloads\extraction\bee\honey\'),
('Q:\desktop\chocolate\london\time\spring\'),
('Q:\documents\cars\astonmartin\vanquish\');
SELECT FolderParse.*
FROM @folders AS f
CROSS APPLY
(
SELECT
FolderPath = f.Folder,
RootDirectory = SUBSTRING(f.Folder, 1, p2.Pos-2),
SubDirectory = SUBSTRING(f.Folder, 1, p3.Pos-1),
SubSubDirectory = SUBSTRING(f.Folder, 1, p4.Pos-1)
FROM (VALUES(CHARINDEX('\',f.Folder))) AS p1(Pos)
CROSS APPLY (VALUES(CHARINDEX('\',f.Folder,p1.Pos 1))) AS p2(Pos)
CROSS APPLY (VALUES(CHARINDEX('\',f.Folder,p2.Pos 1))) AS p3(Pos)
CROSS APPLY (VALUES(CHARINDEX('\',f.Folder,p3.Pos 1))) AS p4(Pos)
) AS folderParse;
結果：
FolderPath RootDirectory SubDirectory SubSubDirectory
----------------------------------------- -------------- ----------------------- ---------------------------------
Q:\downloads\extraction\bee\honey\ Q:\downloads Q:\downloads\extraction Q:\downloads\extraction\bee
Q:\desktop\chocolate\london\time\spring\ Q:\desktop Q:\desktop\chocolate Q:\desktop\chocolate\london
Q:\documents\cars\astonmartin\vanquish\ Q:\documents Q:\documents\cars Q:\documents\cars\astonmartin
解決方案#2 - 理貨表：
第一個解決方案是要走的路，但是當您需要更深入時，第二個解決方案更好。為此，您需要獲取fnTally的副本。
以下是對一個字串執行此操作的方法：
DECLARE @string VARCHAR(1000) = 'Q:\documents\cars\astonmartin\vanquish\';
SELECT
FolderPath = @string,
RootDirectory = MAX(CASE f.RN WHEN 1 THEN f.FPath END),
SubDirectory = MAX(CASE f.RN WHEN 2 THEN f.FPath END),
SubSubDirectory = MAX(CASE f.RN WHEN 3 THEN f.FPath END)
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY t.N), SUBSTRING(@string,1,t.N 2)
FROM (VALUES(SUBSTRING(@string,4,1000))) AS s(Txt)
CROSS APPLY dbo.fnTally(1,LEN(s.Txt)) AS t
WHERE SUBSTRING(s.Txt,t.N,1) = '\'
) AS f(RN,FPath);
回傳：
FolderPath RootDirectory SubDirectory SubSubDirectory
---------------------------------------- ------------------ --------------------- --------------------------------
Q:\documents\cars\astonmartin\vanquish\ Q:\documents Q:\documents\cars Q:\documents\cars\astonmartin
對著一張桌子：
--==== Sample Data
DECLARE @folders TABLE (Folder VARCHAR(1000));
INSERT @folders VALUES
('Q:\downloads\extraction\bee\honey\'),
('Q:\desktop\chocolate\london\time\spring\'),
('Q:\documents\cars\astonmartin\vanquish\');
--==== Solution
SELECT DirectoryParse.*
FROM @folders AS fld
CROSS APPLY
(
SELECT
FolderPath = fld.Folder,
RootDirectory = MAX(CASE f.RN WHEN 1 THEN f.FPath END),
SubDirectory = MAX(CASE f.RN WHEN 2 THEN f.FPath END),
SubSubDirectory = MAX(CASE f.RN WHEN 3 THEN f.FPath END)
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY t.N), SUBSTRING(fld.Folder,1,t.N 2)
FROM (VALUES(SUBSTRING(fld.Folder,4,1000))) AS s(Txt)
CROSS APPLY dbo.fnTally(1,LEN(s.Txt)) AS t
WHERE SUBSTRING(s.Txt,t.N,1) = '\'
) AS f(RN,FPath)
) AS DirectoryParse;
結果：
FolderPath RootDirectory SubDirectory SubSubDirectory
----------------------------------------- -------------- ----------------------- ---------------------------------
Q:\downloads\extraction\bee\honey\ Q:\downloads Q:\downloads\extraction Q:\downloads\extraction\bee
Q:\desktop\chocolate\london\time\spring\ Q:\desktop Q:\desktop\chocolate Q:\desktop\chocolate\london
Q:\documents\cars\astonmartin\vanquish\ Q:\documents Q:\documents\cars Q:\documents\cars\astonmartin
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/349851.html
標籤：sql sql-server 查询语句 子串 字符索引