如何在分隔符和輸入到新列之間多次提取字符串的一部分 T-SQL

亞當·克雷文

我想從具有許多相同分隔符的值中多次提取字符串的一部分，並將提取的字符串放入新列中。

這是我正在處理的數據示例（這些文件夾路徑可能更長，具體取決於文件夾的深度）：

文件夾路徑：

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

我只是想知道這是否可能，尤其是當文件夾名稱具有不同長度的所有類型時。

任何幫助都會讓我非常感激 - 謝謝！

艾倫·伯斯坦

更新（使用第二個解決方案）

解決方案 #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

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。