T-SQL: Напротив конкатенации строк - как разделить строку на несколько записей [дубликат]

Я использую эту функцию (SQL Server 2005 и выше).

create function [dbo].[Split]
(
    @string nvarchar(4000),
    @delimiter nvarchar(10)
)
returns @table table
(
    [Value] nvarchar(4000)
)
begin
    declare @nextString nvarchar(4000)
    declare @pos int, @nextPos int

    set @nextString = ''
    set @string = @string + @delimiter

    set @pos = charindex(@delimiter, @string)
    set @nextPos = 1
    while (@pos <> 0)
    begin
        set @nextString = substring(@string, 1, @pos - 1)

        insert into @table
        (
            [Value]
        )
        values
        (
            @nextString
        )

        set @string = substring(@string, @pos + len(@delimiter), len(@string))
        set @nextPos = @pos
        set @pos = charindex(@delimiter, @string)
    end
    return
end

SELECT substring(commaSeparatedTags,0,charindex(',',commaSeparatedTags))

даст Вам первый тег. Можно продолжить так же получать второй и так далее путем объединения подстроки и charindex один слой глубже каждый раз. Это - непосредственное решение, но оно работает только с очень немногими тегами, поскольку запрос растет очень быстро в размере и становится нечитабельным. Движение к функциям затем, как обрисовано в общих чертах в другом, более сложных ответах на это сообщение.

Я записал это некоторое время назад. Это предполагает, что разделитель является запятой и что отдельные значения не больше, чем 127 символов. Это могло быть изменено довольно легко.

Это обладает преимуществом того, чтобы не быть ограниченным 4 000 символов.

Удачи!

ALTER Function [dbo].[SplitStr] ( 
        @txt text 
) 
Returns @tmp Table 
        ( 
                value varchar(127)
        ) 
as 
BEGIN 
        declare @str varchar(8000) 
                , @Beg int 
                , @last int 
                , @size int 

        set @size=datalength(@txt) 
        set @Beg=1 


        set @str=substring(@txt,@Beg,8000) 
        IF len(@str)<8000 set @Beg=@size 
        ELSE BEGIN 
                set @last=charindex(',', reverse(@str)) 
                set @str=substring(@txt,@Beg,8000-@last) 
                set @Beg=@Beg+8000-@last+1 
        END 

        declare @workingString varchar(25) 
                , @stringindex int 



        while @Beg<=@size Begin 
                WHILE LEN(@str) > 0 BEGIN 
                        SELECT @StringIndex = CHARINDEX(',', @str) 

                        SELECT 
                                @workingString = CASE 
                                        WHEN @StringIndex > 0 THEN SUBSTRING(@str, 1, @StringIndex-1) 
                                        ELSE @str 
                                END 

                        INSERT INTO 
                                @tmp(value)
                        VALUES 
                                (cast(rtrim(ltrim(@workingString)) as varchar(127)))
                        SELECT @str = CASE 
                                WHEN CHARINDEX(',', @str) > 0 THEN SUBSTRING(@str, @StringIndex+1, LEN(@str)) 
                                ELSE '' 
                        END 
                END 
                set @str=substring(@txt,@Beg,8000) 

                if @Beg=@size set @Beg=@Beg+1 
                else IF len(@str)<8000 set @Beg=@size 
                ELSE BEGIN 
                        set @last=charindex(',', reverse(@str)) 
                        set @str=substring(@txt,@Beg,8000-@last) 
                        set @Beg=@Beg+8000-@last+1 

                END 
        END     

        return
END 

Небольшая модификация решения выше, поэтому оно работает с разделителями переменной длины.

create FUNCTION dbo.fn_Split2 (@sep nvarchar(10), @s nvarchar(4000))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + (datalength(@sep)/2), CHARINDEX(@sep, @s, stop + (datalength(@sep)/2))
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS s
    FROM Pieces
  )

NB: я использовал datalength (), поскольку len () неправильно сообщает о наличии конечных пробелов.

Вы также можете достичь этого эффекта, используя XML, как показано здесь, что снимает ограничение представленных ответов, которые все, кажется, включают рекурсию в той или иной форме. Конкретный вариант использования, который я здесь привел, позволяет использовать разделитель длиной до 32 символов, но его можно увеличить до необходимого размера.

create FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX))
RETURNS TABLE
AS
    RETURN
    (
        SELECT r.value('.','VARCHAR(MAX)') as Item
        FROM (SELECT CONVERT(XML, N'<root><r>' + REPLACE(REPLACE(REPLACE(@s,'& ','&amp; '),'<','&lt;'), @sep, '</r><r>') + '</r></root>') as valxml) x
        CROSS APPLY x.valxml.nodes('//root/r') AS RECORDS(r)
    )

Затем вы можете вызвать его, используя:

SELECT * FROM dbo.Split(' ', 'I hate bunnies')

Что возвращает:

-----------
|I        |
|---------|
|hate     |
|---------|
|bunnies  |
-----------

---

Должен отметить, что на самом деле я не ненавижу кроликов... это просто пришло мне в голову по какой-то причине.

---

Следующее - самое близкое, что я смог придумать, используя тот же метод во встроенной функции с табличным значением. НЕ ИСПОЛЬЗУЙТЕ ЭТО, ЭТО УЖАСНО НЕЭФФЕКТИВНО! Приведено для справки.

CREATE FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX))
RETURNS TABLE
AS
    RETURN
    (
        SELECT r.value('.','VARCHAR(MAX)') as Item
        FROM (SELECT CONVERT(XML, N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>') as valxml) x
        CROSS APPLY x.valxml.nodes('//root/r') AS RECORDS(r)
    )

Для частного случая разделения строк на слова я нашел другое решение для SQL Server 2008.

with testTable AS
(
SELECT 1 AS Id, N'how now brown cow' AS txt UNION ALL
SELECT 2, N'she sells sea shells upon the sea shore' UNION ALL
SELECT 3, N'red lorry yellow lorry' UNION ALL
SELECT 4, N'the quick brown fox jumped over the lazy dog'
)

SELECT display_term, COUNT(*) As Cnt
 FROM testTable
CROSS APPLY sys.dm_fts_parser('"' + txt + '"', 1033, 0,0)
GROUP BY display_term
HAVING COUNT(*) > 1
ORDER BY Cnt DESC

Возвращает

display_term                   Cnt
------------------------------ -----------
the                            3
brown                          2
lorry                          2
sea                            2