T Wartościowa tabela SQL Funkcja dzielenia kolumny przecinkami

Napisałem funkcję cenioną w tabeli w Microsoft SQL Server 2008, aby wziąć kolumnę rozdzielaną przecinkami w bazie danych i wypluć osobne wiersze dla każdej wartości.

Przykład: „jeden, dwa, trzy, cztery” zwróci nową tabelę z tylko jedną kolumną zawierającą następujące wartości:

one
two
three
four

Czy ten kod wygląda na podatny na błędy? Kiedy to testuję

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

po prostu działa wiecznie i nigdy nic nie zwraca. Jest to naprawdę rozczarowujące, zwłaszcza, że ​​nie ma wbudowanych funkcji podziału na serwerze MSSQL (DLACZEGO DLACZEGO ?!) i wszystkie podobne funkcje, które znalazłem w sieci, są absolutnym śmieciem lub po prostu nie mają znaczenia dla tego, co próbuję zrobić .

Oto funkcja:

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

Lekko przerobiłem to ...

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

Nie zrobiłbym tego z pętlą; są o wiele lepsze alternatywy. Zdecydowanie najlepsza, gdy trzeba rozdzielić, to CLR, a podejście Adama Machanica jest najszybsze, jakie przetestowałem .

Następnym najlepszym podejściem IMHO, jeśli nie możesz wdrożyć CLR, jest tabela liczb:

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

... która umożliwia tę funkcję:

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

Wierzę, że wszystkie te będą działały lepiej niż funkcja, którą masz, kiedy ją uruchomisz, zwłaszcza, że ​​są one wbudowane zamiast wielu instrukcji. Nie badałem, dlaczego twoja nie działa, ponieważ nie sądzę, żeby warto było włączyć tę funkcję.

Ale to wszystko mówiło ...

Skoro używasz programu SQL Server 2008, czy istnieje powód, dla którego musisz się podzielić? Wolę do tego użyć TVP:

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

Teraz możesz zaakceptować to jako parametr procedur przechowywanych i używać zawartości tak, jak w przypadku 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

I możesz przekazać TVP bezpośrednio z C # itp. Jako DataTable. To prawie na pewno przewyższy każde z powyższych rozwiązań, szczególnie jeśli budujesz w aplikacji ciąg rozdzielany przecinkami, aby procedura składowana mogła wywołać TVP w celu jego ponownego rozdzielenia. Więcej informacji na temat TVP znajduje się w świetnym artykule Erlanda Sommarskoga .

Niedawno napisałem serię o dzieleniu ciągów:

• 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

A jeśli używasz programu SQL Server 2016 lub nowszego (lub bazy danych Azure SQL), dostępna jest nowa STRING_SPLITfunkcja , o której pisałem tutaj na blogu:

• Niespodzianki dotyczące wydajności i założenia: STRING_SPLIT ()
• STRING_SPLIT () w SQL Server 2016: Kontynuacja # 1
• STRING_SPLIT () w SQL Server 2016: Kontynuacja # 2

SQL Server 2016 wprowadził funkcję STRING_SPLIT () . Ma dwa parametry - cięty sznurek i separator. Dane wyjściowe to jeden wiersz na każdą zwróconą wartość.

Dla podanego przykładu

SELECT * FROM string_split('one,two,three,four', ',');

wróci

value
------------------
one
two
three
four

Używam i uwielbiam splitter strunowy Jeffa Modena od czasu, gdy się pojawił.

Tally OH! Ulepszona funkcja „CSV Splitter” SQL 8K

CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

CREATE FUNCTION [dbo].[fnSplit]
(

    @sInputList VARCHAR(8000),         -- List of delimited items

    @sDelimiter VARCHAR(8000) = ','    -- delimiter that separates items

)
RETURNS @List TABLE (colData VARCHAR(8000))

BEGIN

DECLARE @sItem VARCHAR(8000)

    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0

    BEGIN

        SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX
(@sDelimiter,@sInputList,0)-1))),

        @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)
+LEN(@sDelimiter),LEN(@sInputList))))

        IF LEN(@sItem) > 0
            INSERT INTO @List SELECT @sItem
        END

        IF LEN(@sInputList) > 0
            INSERT INTO @List SELECT @sInputList -- Put the last item in
        RETURN
    END

--TEST

--Example 1: select * from fnSplit('1,22,333,444,,5555,666', ',')

--Example 2: select * from fnSplit('1##22#333##444','##')  --note second colData has embedded #

--Example 3: select * from fnSplit('1 22 333 444  5555 666', ' ')