Встроенные функции
Для работы со строка в MySQL определен ряд встроенных функций:
-
CONCAT : объединяет строки. В качестве параметра принимает от 2-х и более строк, которые надо соединить:
SELECT CONCAT('Tom', ' ', 'Smith') -- Tom Smith
SELECT CONCAT_WS(' ', 'Tom', 'Smith', 'Age:', 34) -- Tom Smith Age: 34
SELECT LENGTH('Tom Smith') -- 9
SELECT LTRIM(' Apple')
SELECT RTRIM(' Apple ')
SELECT TRIM(' Tom Smith ')
С помощью дополнительного оператора можно задать где имеено удалить пробелы: BOTH (в начале и в конце), TRAILING (только в конце), LEADING (только в начале):
SELECT TRIM(BOTH FROM ' Tom Smith ')
SELECT LOCATE('om', 'Tom Smith'); -- 2 SELECT LOCATE('m', 'Tom Smith'); -- 3 SELECT LOCATE('m', 'Tom Smith', 4); -- 6 SELECT LOCATE('mig', 'Tom Smith'); -- 0
SELECT LEFT('Apple', 3) -- App
SELECT RIGHT('Apple', 3) -- ple
SELECT SUBSTRING('Galaxy S8 Plus', 8), -- S8 Plus (SELECT SUBSTRING('Galaxy S8 Plus', 8, 2) ); -- S8
SELECT SUBSTRING_INDEX('Galaxy S8 Plus', ' ', 1), -- Galaxy (SELECT SUBSTRING_INDEX('Galaxy S8 Plus', ' ', 2) ), -- Galaxy S8 (SELECT SUBSTRING_INDEX('Galaxy S8 Plus', ' ', -2) ); -- S8 Plus
SELECT REPLACE('Galaxy S8 Plus', 'S8 Plus', 'Note 8') -- Galaxy Note 8
SELECT INSERT('Galaxy S9', 8, 3, 'Note 9'); -- Galaxy Note 9
SELECT REVERSE('123456789') -- 987654321
SELECT LOWER('Apple') -- apple
SELECT UPPER('Apple') -- APPLE
SELECT REPEAT('ab', 5); -- ababababab
SELECT LPAD('Tom Smith', 13, '*'); -- ****Tom Smith
SELECT RPAD('Tom Smith', 13, '*'); -- Tom Smith****
Например, возьмем таблицу:
CREATE TABLE Products ( Id INT AUTO_INCREMENT PRIMARY KEY, ProductName VARCHAR(30) NOT NULL, Manufacturer VARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price DECIMAL NOT NULL );
И при извлечении данных применим строковые функции:
SELECT UPPER(LEFT(Manufacturer,2)) AS Abbreviation, CONCAT(ProductName, ' - ', Manufacturer) AS FullProdName FROM Products ORDER BY Abbreviation
Такие одинаковые, но такие разные: особенности реализации функции CONCAT в разных языках и СУБД
Часто при работе с табличными данными из различных источников для их объединения требуется дополнительная подготовка ключевых столбцов. Например, если в одной таблице реквизиты договора или иного документа находятся в разных столбцах, а в другой – в одном. В подобной ситуации, как и во многих других (а может и во всех) есть два пути решения задачи приведения к одному виду: разделение одного столбца на два и слияние (конкатенация) двух столбцов в один. В настоящей публикации остановлюсь на подробном рассмотрении второго варианта. Работает функция конкатенации строк в разных СУБД (Microsoft SQL Server, Oracle) и других программных средствах (Excel, Python).
Каким бы банальным с точки зрения аналитики данных ИТ-специалистами не был вопрос конкатенации строк, в разных реализациях этой функции есть свои нюансы, знание которых поможет избежать возможных ошибок в работе, особенно тем, кто только начинает пользоваться современными средствами анализа, такими как T-SQL в различных СУБД и Python.
Всем, кто занимается анализом данных, известна функция «СЦЕПИТЬ()» в Excel, которая работает почти идеально, соединяя все входящие строки (строковые переменные) в одну. При этом, если одна из строк пустая, то она пропускается, лишние символы не добавляются (Рис. 1). Других вариантов соединения строк в Excel нет, разве что использование встроенного языка VBA, но в данном случае он не рассматривается.
Если же речь идёт о работе с данными в Microsoft SQL Server, то здесь можно использовать два варианта конкатенации. И именно разница между этими вариантами и является тем нюансом, который может привести к ошибкам и искажению данных. Первый вариант, это использование функции «concat», которая работает аналогично функции «СЦЕПИТЬ()» в Excel. Второй способ – это использование знака «+» для соединения столбцов. Применяя его, можно столкнуться с рядом проблем. Например, если в одном из столбцов будет значение NULL, то в результате соединения также будет пустое значение (Рис. 2). В случае пренебрежения данным фактом можно получить искажённые данные, особенно когда конкатенированный столбец является ключевым при объединении двух таблиц. Также обязательно следует помнить и о том, что при использовании знака «+» всегда нужно учитывать типы данных в столбцах, поскольку для разных типов данных этот знак работает по-разному: в случае с числовыми типами (integer, float и т.д.) произойдёт операция сложения чисел, в текстовых данных выполнится конкатенация, для типов date и datetime также выполнится сложение.
Рассмотрю вышесказанное на примере. Создам таблицу, содержащую id, серию и номер документа с типом данных nvarchar и заполню её некоторыми данными:
create table #table ( id nvarchar(100) null, d_series nvarchar(100) null, d_number nvarchar(100) null) insert into #table values ('001', 'Lss110', '0274786'), ('002', null, '0456868'), ('003', 'Ldr127', null)После этого напишу скрипт с использованием функции «concat()» и знака «+». Результат данного скрипта на рис. 2.
select id, d_series, d_number, concat(d_series, d_number) CONCATENATED, d_series + d_number Concat_with_plus from #table
Также для наглядности создам и заполню такую же таблицу, но типы данных будут установлены числовые (int). Результат показан на рис. 3.
create table #int_table ( id int null, d_series int null, d_number int null) insert into #int_table values (1, 123, 456789), (2, null, 789123), (3, 789, null) select id, d_series, d_number, concat(d_series, d_number) CONCATENATED, d_series + d_number Concat_with_plus from #int_table
Говоря о конкатенации в Microsoft SQL Server, следует упомянуть ещё об одной версии функции «concat()», позволяющей сократить запись и избежать возможных опечаток, когда необходимо вставить разделитель (пробел или другой символ) между соединяемыми столбцами. Для этого в более поздних версия Microsoft SQL Server существует функция «concat_ws()», которая первым аргументом принимает символ разделителя. Таким образом, запросы вида:
select concat(column_1, ' ', column_2, ' ', column_3) CONCATENATED from tableselect column_1 + ' ' + column_2 +' ' + column_3 CONCATENATED from tableможно заменить более коротким:
select concat_ws(' ', column_1, column_2, column_3) CONCATENATED from tableОсобенно актуален этот вариант тогда, когда запросы на сервер формируются динамически и очень критично не превысить максимальное количество символов в скрипте, отправляемом на сервер.
Похожим образом конкатенация строк в столбцах производится и в библиотеке pandas на языке Python. Разница заключается в том, что функция «pandas.concat()» присоединяет к одному датафрейму строки из другого датафрейма, а для соединения столбцов используется знак «+». На практике это выглядит так, как показано на рис. 4. Создам два датафрейма (df1 и df2) и конкатенирую их в третий датафрейм (df3). В результате датафрейм df3 будет содержать строки из df1 и df2.
Если же нужно получить, например, в df3 столбец, содержащий в себе данные из двух других столбцов разделённые символом «_», то код будет выглядеть так, как показано на рис. 5
Если в одном из конкатенируемых столбцов содержится пустое значение (nan или None), то в результирующий столбец будет записано значение nan, аналогично NULL в Microsoft SQL Server.
Ещё один инструмент, который имеет некоторые особенности и о котором следует упомянуть, – это СУБД Oracle. Здесь примечательно то, что функция «concat()» принимает в себя только два аргумента, если нужно объединить несколько столбцов, то необходимо использовать вложенную функцию, а это напрямую сказывается на производительности и скорости исполнения запросов. Поэтому предпочтительнее использовать символ «||», и тогда скрипт выглядит следующим образом:
select column_1 || ' ' || column_2 || ' ' || column_3 CONCATENATED from tableВ заключение хочется подчеркнуть, что даже самая тривиальная функция может быть реализована по-разному в разных средах, инструментах, языках программирования. Поэтому для получения более качественного результата при анализе данных, обязательно нужно учитывать все особенности и различия, пренебрежение которыми может повлечь за собой серьёзные ошибки.
SQL Server функция CONCAT
В SQL Server (Transact-SQL) функция CONCAT позволяет соединять строки.
Синтаксис
Синтаксис функции CONCAT в SQL Server (Transact-SQL):
CONCAT( string1, string2, . string_n )
Параметры или аргументы
string1 , string2 , . string_n — строки для объединения.
Применение
Функция CONCAT может использоваться в следующих версиях SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012
Пример
Рассмотрим некоторые примеры SQL Server функции CONCAT, чтобы понять, как использовать функцию CONCAT в SQL Server (Transact-SQL). Например:
Transact-SQL
SELECT CONCAT ( ‘SQL’ , ‘Server’ );
—Результат: ‘SQLServer’
SELECT CONCAT ( ‘S’ , ‘Q’ , ‘L’ , ‘Server’ );
—Результат: ‘SQLServer’
SELECT CONCAT ( ‘SQL ‘ , ‘Server’ );
—Результат: ‘SQL Server’
Объединение с символом пробел
Когда вы соединяете значения строк, вы можете добавить символ пробела для разделения ваших объединяемых значений. В противном случае вы можете получить длинную строку с объединенными значениями, без разделителя. Это затрудняет чтение результатов.
Рассмотрим простой пример.
Например:
Transact-SQL
SELECT CONCAT ( ‘Жаркое’ , ‘ ‘ , ‘солнце’ );
—Результат: ‘Жаркое солнце’
В этом примере мы использовали второй параметр в функции CONCAT, чтобы добавить символ пробела между значениями Жаркое и солнце.
Объединение с символом ковычки
Так как параметры внутри функции CONCAT заключаются в одинарные кавычки, то нет прямой информации о том, как добавить символ одиночной кавычки в результат функции CONCAT.
Давайте рассмотрим довольно простой пример, который показывает, как добавить одинарную кавычку в результирующую строку, используя функцию CONCAT.
Это можно написать следующим образом:
Функция CONCAT
Для конкатенации строк в Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server используется оператор «+».
Т.е. если операнды являются числовыми, то выполняется операция сложения, а если – строковыми, то конкатенация:
Консоль
Выполнить
Если же операнды являются значениями разных типов, то SQL Server выполняет неявное преобразование типов . Выполняя следующий запрос
Консоль
Выполнить
мы получим сообщение об ошибке:
Error converting data type varchar to real.(Ошибка при преобразовании типа данных varchar к real.)
Существует приоритет типов при их неявном преобразовании, и в соответствии с этим приоритетом сервер пытается преобразовать строку ‘ Gb’ к типу данных столбца hd (real).
Разумеется, явное преобразование типа решает проблему:
Консоль
Выполнить
В SQL Server 2012 появилась функция CONCAT , которая выполняет конкатенацию, неявно преобразуя типы аргументов к строковому типу данных. С помощью этой функции предыдущий запрос можно переписать так:
Консоль
Выполнить
Еще одна полезная особенность функции CONCAT состоит в том, что NULL-значени я неявно преобразуются в пустую строку- ». Обычная же конкатенация с NULL-значением дает NULL. Вот пример, который это демонстрирует.
Консоль
Выполнить
Следует отметить, что у функции CONCAT может быть произвольное число аргументов, но не менее двух.
Консоль
Выполнить
MySQL
В MySQL также имеется функция CONCAT, вернее, даже две функции. Первая из них – CONCAT – возвращает NULL, если среди аргументов функции встречается NULL, вторая – CONCAT_WS – опускает аргумент, если его значение NULL. Кроме того, эта функция первым аргументом имеет разделитель, используемый при конкатенации.
Oracle
В Oracle функция CONCAT имеет только два аргумента, поэтому запрос
работать не будет. Однако можно применить функцию несколько раз:
Это, конечно, делает ее менее удобной по сравнению со стандартным способом. Обратите внимание, что при конкатенации чисел выполняется неявное преобразование к символьному типу данных:
При этом оба метода игнорируют NULL-значения:
PostgreSQL
Функция CONCAT в PostgreSQL работает так же, как и в SQL Server.
Заметим, что запрос, который работает в Oracle
будет вызывать ошибку в PostgreSQL, поскольку неявного преобразования типа здесь не происходит. Конкатенировать (стандартным способом) следует символьные значения:
Еще одним отличием от поведения в Oracle является конкатенация с NULL: