Из строки в таблицу. Функция split

Как разделить строку в SQL Server: от новичка до профи

В SQL Server 2016 и новее всё просто. Используйте встроенную функцию string_split. Она быстрая, надёжная и не требует лишних телодвижений.

-- Простейший пример: разбиваем список ID
SELECT * FROM STRING_SPLIT('123,434,245,1231,132', ',')

-- А если строки с пробелами? Ничего страшного:
SELECT * FROM STRING_SPLIT('яблоко, груша, банан', ',')

-- Результат: каждая фрукт на отдельной строке

Что если у вас SQL Server 2014 или старше?

Тут string_split уже не сработает. Придётся писать свою функцию. Вот проверенный вариант, который я использую в проектах:

ALTER FUNCTION [dbo].[split] (
    @stringToSplit NVARCHAR(MAX), 
    @char NVarCHAR
)
RETURNS @returnList TABLE (
    [value] [nvarchar] (max), 
    num int
)
AS
BEGIN
    Declare @r1 TABLE ([value] [nvarchar] (max), num int)
    DECLARE @name NVARCHAR(max)
    DECLARE @pos INT
    declare @index int = 1

    WHILE CHARINDEX(@char, @stringToSplit) > 0
    BEGIN
        SELECT @pos = CHARINDEX(@char, @stringToSplit)
        SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

        INSERT INTO @r1
        SELECT @name, @index

        set @index = @index + 1
        SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
    END

    INSERT INTO @r1
    SELECT @stringToSplit, @index

    insert into @returnList
    select [Value], num from @r1 order by num

    RETURN
END

Боевой пример: фильтрация по списку ID

-- Допустим, пользователь ввёл: "5,12,23,45"
-- А нам нужно выбрать только этих клиентов

DECLARE @ids NVARCHAR(MAX) = '5,12,23,45'

-- Для SQL Server 2016+
SELECT * 
FROM Customers 
WHERE CustomerID IN (
    SELECT value FROM STRING_SPLIT(@ids, ',')
)

-- Для старых версий
SELECT * 
FROM Customers 
WHERE CustomerID IN (
    SELECT value FROM dbo.split(@ids, ',')
)

Тест производительности: когда разница критична

Я провёл небольшой тест. Строка из 30 000 элементов — и вот что получилось:

declare @s nvarchar(max)='', @i int =0
while (@i < 30000) begin
    set @s = @s+ cast(@i as nvarchar) + ','
    set @i = @i +1
end

select @s

-- Встроенная функция: около 3 секунд
select * from STRING_SPLIT(@s, ',')   

-- Самописная функция: около 6 секунд
select * from dbo.split(@s, ',')

На больших объёмах разница в два раза. Поэтому если есть возможность — обновляйтесь до SQL Server 2016 и выше. Сэкономите время и нервы.

Пара лайфхаков из практики

• Всегда проверяйте, не пустая ли строка пришла на вход. Иначе получите мусор в результате.
• Для string_split порядок элементов не гарантируется. Если важен порядок — добавляйте номер строки через ROW_NUMBER().
• Самописная функция возвращает результат с нумерацией. Это удобно, когда нужно сохранить исходный порядок элементов.

И последнее: не пишите велосипеды, если есть готовое решение. string_split — это стандарт индустрии. Используйте его везде, где можно.