Разделение данных в столбцах по запятой: как сделать

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

Если ваша задача – преобразовать строки, разделенные запятыми, в отдельные колонки в SQL Server 2016 и более поздних версиях, вы можете использовать функцию STRING_SPLIT:

SELECT value
FROM STRING_SPLIT((SELECT YourColumn FROM YourTable), ',');

В более старых версиях SQL Server, а также в других базах данных вы можете применить рекурсивное CTE:

WITH RecursiveCTE AS (
  SELECT
    LEFT(YourColumn, CHARINDEX(',', YourColumn + ',') – 1) AS Piece,
    STUFF(YourColumn, 1, CHARINDEX(',', YourColumn + ','), '') AS RemainingParts
  FROM YourTable
  UNION ALL
  SELECT
    LEFT(RemainingParts, CHARINDEX(',', RemainingParts + ',') – 1),
    STUFF(RemainingParts, 1, CHARINDEX(',', RemainingParts + ','), '')
  FROM RecursiveCTE
  WHERE RemainingParts > ''
)
SELECT Piece
FROM RecursiveCTE;

Не забывайте заменить в запросе названия вашего столбца и таблицы. Если функция STRING_SPLIT недоступна, рекурсивное CTE поможет решить поставленную задачу.

Исследуем различные подходы

Пришло время расширить наши SQL-горизонты и попробовать различные методы работы со строками.

Знакомьтесь: split_part() из PostgreSQL

Функция split_part() в PostgreSQL позволяет разделить текст по указанному разделителю и извлечь подстроки:

SELECT
  split_part(YourColumn, ',', 1) AS col1,
  split_part(YourColumn, ',', 2) AS col2,
  ...
FROM YourTable;

Однако в случае с пустой строкой функция вернёт также пустую строку. Обратите внимание на эту особенность при работе с данными.

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

Преобразование столбцов в массивы средствами string_to_array()

Если вы работаете с PostgreSQL и не желаете указывать точные позиции, можете воспользоваться функцией string_to_array:

SELECT
  string_to_array(YourColumn, ',')[1] AS col1,
  string_to_array(YourColumn, ',')[2] AS col2,
  ...
FROM YourTable;

Тем не менее, использование столбцов CSV не является лучшим выбором. Гораздо лучше переорганизовать структуру данных, чтобы избежать сложностей в обслуживании и поддержке.

Создание собственных функций

Создание пользовательskой функции позволяет упростить переиспользование логики обработки:

CREATE FUNCTION split_csv (input_string text, delimiter text, part_number integer)
RETURNS text AS $$
BEGIN
  RETURN split_part(input_string, delimiter, part_number);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Использование
SELECT split_csv(YourColumn, ',', 1) FROM YourTable;

Собственные функции помогают поддерживать код в аккуратном и организованном виде.

Вопросы производительности

Уделяйте внимание производительности, особенно при обработке большого объема данных. Тестирование станет вашим надёжным партнёром в обеспечении быстродействия системы.

Возможные трудности

Особенности обработки пустых строк

Пустые строки могут появится при разделении данных, особенно если исходные данные неравномерны.

Использование массивов и регулярных выражений

В PostgreSQL функция regexp_split_to_array пригодна при работе со сложными разделителями:

SELECT
  (regexp_split_to_array(YourColumn, ', *'))[1] AS col1,
  (regexp_split_to_array(YourColumn, ', *'))[2] AS col2
FROM YourTable;

Однако стоит учесть, что использование регулярных выражений может повысить нагрузку на систему.

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

Если представить ваши данные как сэндвич, где начинка разделена запятыми:

До: [🥪 (сыр,помидор,салат,ветчина)]

То применение SQL позволяет разделить этот сэндвич на составляющие:

-- Мастерский разрез с помощью SQL
SELECT
  SPLIT_PART(column_name, ',', 1) AS ingredient1,
  SPLIT_PART(column_name, ',', 2) AS ingredient2,
  ...

В итоге, получается:

После: [🧀, 🍅, 🥬, 🍖]

Теперь каждый компонент доступен для отдельного анализа, как и данные в столбцах.

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

1. STRING_SPLIT (Transact-SQL) – SQL Server | Microsoft Learn — официальная документация по функции STRING_SPLIT для SQL Server.
2. Использование анализатора лучших практик SQL Server 2008 R2 — руководство к анализатору лучших практик SQL Server.
3. Руководство по функциям обработки строк MySQL 8.0 — справочник функций MySQL, включая FIND_IN_SET.
4. INSTR — информация о функции INSTR в Oracle SQL.
5. Скалярные функции SQLite — обзор функций SQLite для работы со строками.
6. Amazon Redshift — подробное руководство по использованию хранилища данных Amazon Redshift.