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

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

這是我正在處理的資料示例（這些檔案夾路徑可能更長，具體取決于檔案夾的深度）：

檔案夾路徑：

Q:\downloads\extraction\bee\honey\
Q:\desktop\chocolate\london\time\spring\
Q:\documents\cars\astonmartin\vanquish\

我想從左側提取分隔符之間的整個字串，并將它們放入字串不同階段的新列中，例如

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

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

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

標籤：sql sql-server 查询语句 子串 字符索引

上一篇：SQL：優先考慮MAX計算

下一篇：合并SQL查詢結果，其中任何為空