T SQL 表值函式以逗號分隔列

我在 Microsoft SQL Server 2008 中編寫了一個表值函式，以在數據庫中使用逗號分隔的列來為每個值輸出單獨的行。

例如：“一、二、三、四”將返回一個新表，其中只有一列包含以下值：

one
two
three
four

這段程式碼看起來容易出錯嗎？當我用

SELECT * FROM utvf_Split('one,two,three,four',',') 

它永遠執行，永遠不會返回任何東西。這真的很令人沮喪，特別是因為 MSSQL 伺服器上沒有內置的拆分函式（為什麼為什麼為什麼？！），而我在網上找到的所有類似函式都是絕對垃圾，或者與我正在嘗試做的事情無關.

這是功能：

USE *myDBname*
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[utvf_SPlit] (@String VARCHAR(MAX), @delimiter CHAR)

RETURNS @SplitValues TABLE
(
   Asset_ID VARCHAR(MAX) NOT NULL
)

AS
BEGIN
           DECLARE @FoundIndex INT
           DECLARE @ReturnValue VARCHAR(MAX)

           SET @FoundIndex = CHARINDEX(@delimiter, @String)

           WHILE (@FoundIndex <> 0)
           BEGIN
                 DECLARE @NextFoundIndex INT
                 SET @NextFoundIndex = CHARINDEX(@delimiter, @String, @FoundIndex+1)
                 SET @ReturnValue = SUBSTRING(@String, @FoundIndex,@NextFoundIndex-@FoundIndex)
                 SET @FoundIndex = CHARINDEX(@delimiter, @String)
                 INSERT @SplitValues (Asset_ID) VALUES (@ReturnValue)
           END

           RETURN
END

稍微重做了…

DECLARE @FoundIndex INT
DECLARE @ReturnValue VARCHAR(MAX)

SET @FoundIndex = CHARINDEX(@delimiter, @String)

WHILE (@FoundIndex <> 0)
BEGIN
     SET @ReturnValue = SUBSTRING(@String, 0, @FoundIndex)
     INSERT @SplitValues (Asset_ID) VALUES (@ReturnValue)
     SET @String = SUBSTRING(@String, @FoundIndex + 1, len(@String) - @FoundIndex)
     SET @FoundIndex = CHARINDEX(@delimiter, @String)
END

INSERT @SplitValues (Asset_ID) VALUES (@String)

RETURN

我不會用循環來做這個；還有更好的選擇。到目前為止，當你必須拆分時，最好的是 CLR，Adam Machanic 的方法是我測試過的最快的方法。

下一個最好的方法恕我直言，如果你不能實現 CLR，是一個數字表：

SET NOCOUNT ON;

DECLARE @UpperLimit INT = 1000000;

WITH n AS
(
   SELECT
       x = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
   FROM       sys.all_objects AS s1
   CROSS JOIN sys.all_objects AS s2
   CROSS JOIN sys.all_objects AS s3
)
SELECT Number = x
 INTO dbo.Numbers
 FROM n
 WHERE x BETWEEN 1 AND @UpperLimit
 OPTION (MAXDOP 1); -- protecting from Paul White's observation

GO
CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers(Number) 
   --WITH (DATA_COMPRESSION = PAGE);
GO

…允許此功能：

CREATE FUNCTION dbo.SplitStrings_Numbers
(
  @List       NVARCHAR(MAX),
  @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN
  (
      SELECT Item = SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
      FROM dbo.Numbers
      WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
  );
GO

我相信所有這些都會比你擁有的功能更好，當你讓它工作時，特別是因為它們是內聯而不是多語句。我沒有調查你的為什麼不工作，因為我認為讓這個功能工作是不值得的。

但這一切都說…

既然您使用的是 SQL Server 2008，那麼您是否有理由首先需要拆分？我寧願為此使用 TVP：

CREATE TYPE dbo.strings AS TABLE
(
 string NVARCHAR(4000)
);

現在您可以接受它作為儲存過程的參數，並像使用 TVF 一樣使用內容：

CREATE PROCEDURE dbo.foo
 @strings dbo.strings READONLY
AS
BEGIN
 SET NOCOUNT ON;

 SELECT Asset_ID = string FROM @strings;
 -- SELECT Asset_ID FROM dbo.utvf_split(@other_param, ',');
END

您可以直接從 C# 等中將 TVP 作為 DataTable 傳遞。這幾乎肯定會優於上述任何解決方案，特別是如果您在應用程序中專門建構一個逗號分隔的字元串，以便您的儲存過程可以呼叫 TVP 再次將其拆分。有關 TVP 的更多資訊，請參閱Erland Sommarskog 的精彩文章。

最近，我寫了一個關於拆分字元串的系列文章：

• http://sqlperformance.com/2012/07/t-sql-queries/split-strings
• http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up
• http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

如果您使用的是 SQL Server 2016 或更新版本（或 Azure SQL 數據庫），則有一個新STRING_SPLIT功能，我在部落格中對此進行了介紹：

• 性能驚喜和假設：STRING_SPLIT()
• SQL Server 2016 中的 STRING_SPLIT()：後續 #1
• SQL Server 2016 中的 STRING_SPLIT()：後續 #2

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