Удалить строку, разделенную запятыми, из другого значения столбца, разделенного запятой, в SQL [duplicate]

DECLARE @id_list VARCHAR(MAX) = '1234,23,56,576,1231,567,122,87876,57553,1216'
DECLARE @table TABLE ( id VARCHAR(50) )
DECLARE @x INT = 0
DECLARE @firstcomma INT = 0
DECLARE @nextcomma INT = 0

SET @x = LEN(@id_list) - LEN(REPLACE(@id_list, ',', '')) + 1 -- number of ids in id_list

WHILE @x > 0
    BEGIN
        SET @nextcomma = CASE WHEN CHARINDEX(',', @id_list, @firstcomma + 1) = 0
                              THEN LEN(@id_list) + 1
                              ELSE CHARINDEX(',', @id_list, @firstcomma + 1)
                         END
        INSERT  INTO @table
        VALUES  ( SUBSTRING(@id_list, @firstcomma + 1, (@nextcomma - @firstcomma) - 1) )
        SET @firstcomma = CHARINDEX(',', @id_list, @firstcomma + 1)
        SET @x = @x - 1
    END

SELECT  *
FROM    @table

Функция

CREATE FUNCTION dbo.SplitToRows (@column varchar(100), @separator varchar(10))
RETURNS @rtnTable TABLE
  (
  ID int identity(1,1),
  ColumnA varchar(max)
  )
 AS
BEGIN
    DECLARE @position int = 0
    DECLARE @endAt int = 0
    DECLARE @tempString varchar(100)

    set @column = ltrim(rtrim(@column))

    WHILE @position<=len(@column)
    BEGIN       
        set @endAt = CHARINDEX(@separator,@column,@position)
            if(@endAt=0)
            begin
            Insert into @rtnTable(ColumnA) Select substring(@column,@position,len(@column)-@position)
            break;
            end
        set @tempString = substring(ltrim(rtrim(@column)),@position,@endAt-@position)

        Insert into @rtnTable(ColumnA) select @tempString
        set @position=@endAt+1;
    END
    return
END

Использовать регистр

select * from dbo.SplitToRows('T14; p226.0001; eee; 3554;', ';')

Или просто выбрать с несколькими результирующими наборами

DECLARE @column varchar(max)= '1234; 4748;abcde; 324432'
DECLARE @separator varchar(10) = ';'
DECLARE @position int = 0
DECLARE @endAt int = 0
DECLARE @tempString varchar(100)

set @column = ltrim(rtrim(@column))

WHILE @position<=len(@column)
BEGIN       
    set @endAt = CHARINDEX(@separator,@column,@position)
        if(@endAt=0)
        begin
        Select substring(@column,@position,len(@column)-@position)
        break;
        end
    set @tempString = substring(ltrim(rtrim(@column)),@position,@endAt-@position)

    select @tempString
    set @position=@endAt+1;
END

Наконец, ожидание закончилось с SQL Server 2016. Они ввели функцию строки Split, STRING_SPLIT :

select OtherID, cs.Value --SplitData
from yourtable
cross apply STRING_SPLIT (Data, ',') cs

Все остальные методы разделения строки например, XML, таблица Tally, while loop и т. д. были сдуты этой функцией STRING_SPLIT.

Вот отличная статья с сопоставлением производительности: Сюрпризы производительности и предположения: STRING_SPLIT .

Для более старых версий использование таблицы tally table здесь - это одна функция разделения строк (наилучший возможный подход)

CREATE FUNCTION [dbo].[DelimitedSplit8K]
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
     -- enough to cover NVARCHAR(4000)
  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
;

Ссылка на Tally OH! Улучшенная функция SQL 8K «CSV Splitter»

;WITH tmp(SomeID, OtherID, DataItem, Data) as (
    SELECT SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1),
        STUFF(Data, 1, CHARINDEX(',',Data+','), '')
FROM Testdata
WHERE Data > ''
)
SELECT SomeID, OtherID, Data
FROM tmp
ORDER BY SomeID

с незначительной модификацией вышеприведенного запроса ...

При использовании этого подхода вы должны убедиться, что ни одно из ваших значений не содержит что-то, что было бы незаконным. XML-user1151923

Я всегда использую метод XML. Убедитесь, что вы используете VALID XML. У меня есть две функции для преобразования между действительным XML и текстом. (Я, как правило, вырезаю каретки, как мне обычно не нужны.

CREATE FUNCTION dbo.udf_ConvertTextToXML (@Text varchar(MAX)) 
    RETURNS varchar(MAX)
AS
    BEGIN
        SET @Text = REPLACE(@Text,CHAR(10),'')
        SET @Text = REPLACE(@Text,CHAR(13),'')
        SET @Text = REPLACE(@Text,'<','&lt;')
        SET @Text = REPLACE(@Text,'&','&amp;')
        SET @Text = REPLACE(@Text,'>','&gt;')
        SET @Text = REPLACE(@Text,'''','&apos;')
        SET @Text = REPLACE(@Text,'"','&quot;')
    RETURN @Text
END


CREATE FUNCTION dbo.udf_ConvertTextFromXML (@Text VARCHAR(MAX)) 
    RETURNS VARCHAR(max)
AS
    BEGIN
        SET @Text = REPLACE(@Text,'&lt;','<')
        SET @Text = REPLACE(@Text,'&amp;','&')
        SET @Text = REPLACE(@Text,'&gt;','>')
        SET @Text = REPLACE(@Text,'&apos;','''')
        SET @Text = REPLACE(@Text,'&quot;','"')
    RETURN @Text
END