用空格分隔的 SQL 拆分行數據

我正在尋找一個查詢來查找列表中的第 n 個值。分隔符是大於或等於 2 個空格的任何內容。（可以是 3 個或 5 個空格）。盡量避免標量值函式，因為性能可能會更慢。句子可以有任意數量的單詞，從 5 到 20。

CREATE TABLE dbo.TestWrite (TestWriteId int primary key identity(1,1), 
                           TextRow varchar(255))
INSERT INTO dbo.TestWrite (TextRow)
SELECT 'I am writing SQL Code.'
UNION ALL
SELECT 'SQL keywords include join, except, where.'


+-----+----------+---------+---------------+---------+----------+
| SQL | keywords | include |     join,     | except, |   where. |
+-----+----------+---------+---------------+---------+----------+
| I   | am       | writing |    SQL  Code. |         |          |
+-----+----------+---------+---------------+---------+----------+

想要在帶有列的單獨行中，請參閱上面的評論。

這可能是試圖利用的一種解決方案。 https://stackoverflow.com/questions/19449492/using-t-sql-return-nth-delimited-element-from-a-string

DECLARE @dlmt NVARCHAR(10)=N' ';
DECLARE @pos INT = 2;
SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')

如果每行有不同數量的元素，則拆分為結果集的單獨欄位有點棘手。SQL#庫（我編寫的）中有一個 SQLCLR 儲存過程，雖然不在免費版本中，但它確實根據正則表達式分隔符將不同元素的分隔字元串分解為結果集欄位。RegEx 的分隔符使得將“一個或多個空格”視為分隔符非常容易：\s+. 例如：

EXEC SQL#.String_SplitResultIntoFields N'
SELECT [TextRow] FROM #TestWrite;',
N'\s+', NULL, NULL;
/*
Field1    Field2     Field3     Field4    Field5
I         am         writing    SQL       Code.
SQL       keywords   include    join,     except,
*/

如您所見，它根據第一行確定結果集的欄位數，這就是為什麼沒有“Field6”來包含第二行中的最終單詞的原因。

當然，您始終可以使用由空格分隔的破折號來播種第一行以強制一定數量的欄位，但是沒有辦法過濾掉該初始行：

EXEC SQL#.String_SplitResultIntoFields N'
SELECT N''- - - - - - - -''
UNION ALL
SELECT [TextRow] FROM #TestWrite;',
N'\s+', NULL, NULL;
/*
Field1    Field2     Field3     Field4    Field5    Field6   Field7    Field8
-         -          -          -         -         -        -         -
I         am         writing    SQL       Code.
SQL       keywords   include    join,     except,   where.
*/

我想我可以很容易地添加一個可選的輸入參數@ForceResultSetFieldCount，但到今天它還沒有。

如果這個請求只是問題中最初陳述的內容（即“我正在尋找一個查詢來查找列表中的第 n 個值”），那麼這不僅是微不足道的（即使有“一個或多個空格”分隔符要求） ，但這需要一個正則表達式函式RegEx_CaptureGroupCapture，並且在SQL#的免費版本中。例如：

設置

CREATE TABLE #TestWrite
(
 TestWriteId INT PRIMARY KEY IDENTITY(1, 1),
 TextRow VARCHAR(255)
);

INSERT INTO #TestWrite (TextRow)
 SELECT 'I am     writing SQL                   Code.'
 UNION ALL
 SELECT 'SQL  keywords include   join,    except, where.';

測試

正如您在下面看到的，您可以使用“一個或多個單詞字元”的模式，這將排除空格和標點符號（第一個範例），或者您可以使用“一個或多個非空格”的模式，這將包括標點符號等（第二個範例）。

-- only get "word" characters:
SELECT SQL#.RegEx_CaptureGroupCapture(t.[TextRow], N'(\w+)', 1, 4, 1, NULL, 1, -1, NULL)
FROM   #TestWrite t;
/*
SQL
join
*/

-- get non-whitespace:
SELECT SQL#.RegEx_CaptureGroupCapture(t.[TextRow], N'([^\s]+)', 1, 4, 1, NULL, 1, -1, NULL)
FROM   #TestWrite t;
/*
SQL
join,
*/

獲得預期結果的一種可能方法是基於 JSON。需要將輸入數據轉換為有效的 JSON 數組（I am writing SQL Code.轉換為）並使用OPENJSON()["I","am","writing","SQL","Code."]解析該數組。這裡重要的部分是這樣一個事實，即在解析 JSON 數組時，JSON 文本中元素的索引作為鍵（從 0 開始）返回。請注意，這不是一個選項，因為如文件中所述，輸出行可能按任何順序排列，並且不能保證該順序與輸入字元串中子字元串的順序相匹配。OPENJSON()``STRING_SPLIT()

桌子：

CREATE TABLE #TestWrite (
  TestWriteId int primary key identity(1,1), 
  TextRow varchar(255)
)
INSERT INTO #TestWrite
  (TextRow)
VALUES
  ('I    am    writing       SQL  Code.'),
  ('SQL   keywords    include'),
  ('One space as first delimiter,  then  more spaces are used.')

陳述：

DECLARE @dlmt varchar(10) = '  ';
DECLARE @pos int = 2;

WITH cte AS (
  SELECT 
     t.TestWriteId,
     t.[TextRow],
     ROW_NUMBER() OVER (PARTITION BY t.TestWriteId ORDER BY CONVERT(int, j.[key])) AS Rn,
     j.[key],
     j.[word]
  FROM #TestWrite t
  CROSS APPLY (
     SELECT [key], LTRIM(RTRIM([value])) AS [word]
     FROM OPENJSON('["' +  REPLACE(t.TextRow, @dlmt, '","') + '"]')
     WHERE LTRIM(RTRIM([value])) <> ''
  ) j
)
SELECT
  TestWriteId,
  TextRow,
  word
FROM cte
WHERE Rn = @pos

輸出：

TestWriteId TextRow                                                     word
1           I    am    writing       SQL  Code.                         am
2           SQL   keywords    include                                   keywords
3           One space as first delimiter,  then  more spaces are used.  then

引用自：https://dba.stackexchange.com/questions/231009