Разделение строки на имя и фамилию в T-SQL при отсутствии разделителя

Пройдите тест, узнайте какой профессии подходите

Я предпочитаю

Работать самостоятельно и не зависеть от других

Работать в команде и рассчитывать на помощь коллег

Организовывать и контролировать процесс работы

Быстрый ответ

Для выполнения операции разделения строки в T-SQL можно воспользоваться функцией STRING_SPLIT. Этой функции требуется передать два аргумента: саму строку и разделитель:

SQL

Скопировать код

SELECT value FROM STRING_SPLIT('sample,text,to,split', ',')

В результате такого запроса мы получим отдельные строки: 'sample', 'text', 'to', 'split'. Функция доступна, начиная с версии SQL Server 2016.

Версия SQL Server до 2016 года также имеет свое решение проблемы разделения строк, поэтому просим вас продолжить чтение – найдутся полезные советы для каждого.

Кинга Идем в IT: пошаговый план для смены профессии

SQL Server до 2016: Разделение строк сложным способом

Предыдущие версии SQL Server не обладали встроенной функцией для разделения строк. Однако, это дело исправимо – хоть и с некоторыми заморочками.

Применение CHARINDEX и SUBSTRING

В случае недоступности функции STRING_SPLIT для разделения строк в старых версиях SQL Server можно применять другие встроенные функции:

SQL

Скопировать код

-- Отправимся немного в прошлое.
-- И начнём наше разделительное приключение!
DECLARE @string NVARCHAR(100) = 'FirstName:LastName',
        @delimiter CHAR(1) = ':';

SELECT CASE 
         WHEN CHARINDEX(@delimiter, @string) > 0 THEN 
           SUBSTRING(@string, 1, CHARINDEX(@delimiter, @string) – 1) 
         ELSE 
           @string 
       END AS FirstName,
       CASE 
         WHEN CHARINDEX(@delimiter, @string) > 0 THEN 
           SUBSTRING(@string, CHARINDEX(@delimiter, @string) + 1, LEN(@string)) 
         ELSE 
           NULL 
       END AS LastName;
-- NULL в фамилии? Это мы легко исправим.

Подробнее об этом расскажет наш спикер на видео

Решение с несколькими разделителями: Рекурсивные CTE

Если разделителей больше одного, можно применить рекурсивный CTE (Common Table Expression) для разбиения строки:

SQL

Скопировать код

-- Время для "разделительного бинго"!
-- Рекурсивные CTE, словно лучшие союзники
WITH SplitCTE AS (
    SELECT 0 AS StartPos, CHARINDEX(',', @string + ',') AS EndPos
    UNION ALL
    SELECT EndPos + 1, CHARINDEX(',', @string + ',', EndPos + 1)
    FROM SplitCTE
    WHERE EndPos > 0
)
SELECT SUBSTRING(@string, StartPos, EndPos – StartPos) AS Value
FROM SplitCTE
WHERE EndPos > StartPos;
-- И вуаля – подстроки, как из рога изобилия!

Визуализация

Строку разделяем аналогично тому, как бьём шоколад на кусочки, причем каждый излом соответствует разделителю:

Markdown

Скопировать код

Строка: "SQL,Tutorial,Split,String,Function"

С применением функции STRING_SPLIT:

SQL

Скопировать код

SELECT value FROM STRING_SPLIT('SQL,Tutorial,Split,String,Function', ',');

Строка до и после разделения представлена так:

Markdown

Скопировать код

До разделения: 🍫🍯🍫🍯🍫🍯🍫🍯🍫
После разделения:  🍫  🍫  🍫  🍫  🍫

Каждый кусочек шоколада – это подстрока, полученная при разделении исходной строки по карамельным разделителям.

Применение LEFT и RIGHT для разделения

Зная позицию разделителя, можно применить функции LEFT и RIGHT для "резки" строк:

SQL

Скопировать код

-- Разделка как искусство
SELECT LEFT(@string, CHARINDEX(@delimiter, @string) – 1) AS FirstPart,
       RIGHT(@string, LEN(@string) – CHARINDEX(@delimiter, @string)) AS SecondPart;
-- Вот ваш кусочек данных!

Гибкость UNION ALL

Для разбиения длинной строки на четкие и управляемые части весьма полезен UNION ALL:

SQL

Скопировать код

-- Готовы к феерии разделения строк!
WITH Numbers AS (
    SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) num(n)
),
Sequences AS (
    SELECT a.n + (10 * b.n) + (100 * c.n) AS Number
    FROM Numbers a, Numbers b, Numbers c
)
SELECT SUBSTRING(@string, Number, CHARINDEX(@delimiter, @string + @delimiter, Number) – Number) AS Value
FROM Sequences
WHERE Number <= LEN(@string)
AND SUBSTRING(@delimiter + @string, Number, 1) = @delimiter
-- UNION ALL: не просто SQL-команда, но и философия жизни.

Ограда от null и пустых строк

Обрабатывать возможные null или пустые строки очень важно, устанавливая значения по умолчанию с использованием функций ISNULL или COALESCE:

SQL

Скопировать код

-- Потому что никто не болеет любовью к неожиданным NULL'ам
SELECT ISNULL(SUBSTRING(@string, Number, CHARINDEX(@delimiter, @string + @delimiter, Number) – Number), '') AS Value
FROM Sequences
-- Это было эффектное управление данными, шоу завершено!

Полезные материалы

STRING_SPLIT (Transact-SQL) – SQL Server | Microsoft Learn – Лучшие практики при работе с функцией STRING_SPLIT.
T-SQL Window functions – SQLServerCentral – Продвинутые методы T-SQL, которые переведут ваши запросы на новый уровень.
Split Comma Separated String – SQL Authority – Пинал Дэйв покажет способы разделения строк без применения функций.

Проверь как ты усвоил материалы статьи

Пройди тест и узнай насколько ты лучше других читателей

Какой T-SQL функции следует использовать для разделения строки начиная с SQL Server 2016?

1 / 5

Свежие материалы

Вопросы по SQL на собеседовании для тестировщиков

6 сентября 2024

Системные требования для установки Swift

6 сентября 2024

Критика и ограничения HTTP

6 сентября 2024