Урок 3.2: Основные строковые функции в SQL

Строковые функции в SQL используются для манипуляции и преобразования текстовых данных. Эти функции необходимы для очистки, форматирования и извлечения информации из текстовых столбцов в базе данных. В этом уроке рассматриваются некоторые из наиболее часто используемых строковых функций и приводятся практические примеры.

Общие строковые функции

UPPER() - Преобразует строку в верхний регистр.

Синтаксис:

UPPER(string)

Пример:

SELECT UPPER(first_name) AS uppercase_name
FROM employees;

Результат: Преобразует все значения first_name в верхний регистр.

LOWER() - Преобразует строку в нижний регистр.

Синтаксис:

LOWER(string)

Пример:

SELECT LOWER(last_name) AS lowercase_name
FROM employees;

Результат: Преобразует все значения last_name в нижний регистр.

LENGTH(), CHAR_LENGTH() или LEN() - Возвращает длину строки (в символах или байтах, в зависимости от СУБД).

Синтаксис:

CHAR_LENGTH(string) -- Количество символов (например, в MySQL)
LENGTH(string)      -- В MySQL: длина в байтах
LEN(string)         -- В SQL Server: длина в символах

Важно: в разных СУБД "длина строки" может означать разное. Одни функции возвращают длину в символах, другие - в байтах. Поэтому всегда проверяйте, в каких единицах работает конкретная функция в вашей СУБД.

Когда LENGTH() и CHAR_LENGTH() могут отличаться (например, в MySQL):

• Для строк только с латиницей и цифрами значения часто совпадают.
• Для строк с многобайтовыми символами (кириллица, эмодзи, иероглифы) LENGTH() обычно больше CHAR_LENGTH(), потому что считает байты.

Короткий пример:

• 'SQL': LENGTH = 3, CHAR_LENGTH = 3
• 'Привет': LENGTH = 12, CHAR_LENGTH = 6

Пример:

SELECT CHAR_LENGTH(product_name) AS name_length
FROM products;

Результат: Возвращает количество символов в каждом значении product_name.

SUBSTRING() или SUBSTR() - Извлекает часть строки.

Синтаксис:

SUBSTRING(string, start_position, length) -- Для большинства баз данных
SUBSTR(string, start_position, length)    -- Для Oracle и других

Пример:

SELECT SUBSTRING(email, 1, 5) AS email_prefix
FROM users;

Результат: Извлекает первые 5 символов из столбца email.

CONCAT() - Объединяет две или более строк в одну.

Синтаксис:

CONCAT(string1, string2, ...)

Пример:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

Результат: Объединяет first_name и last_name в одно значение full_name.

Важно: поведение CONCAT() при NULL зависит от СУБД. Например, в MySQL и MariaDB, если хотя бы один аргумент равен NULL, результатом CONCAT() тоже будет NULL.

CONCAT_WS() - Объединяет строки, вставляя между ними разделитель, и пропускает NULL-значения.

Синтаксис:

CONCAT_WS(separator, string1, string2, ...)

Пример:

SELECT CONCAT_WS(' ', first_name, last_name) AS full_name
FROM employees;

Результат: Объединяет first_name и last_name через пробел, пропуская NULL-значения в аргументах.

Если нужно NULL-safe объединение без разделителя, можно использовать CONCAT_WS('', string1, string2, ...).

TRIM() - Удаляет начальные и конечные символы из строки, чаще всего пробелы.

Синтаксис:

TRIM(string)
TRIM([characters FROM] string)

Пример:

SELECT TRIM('   SQL Basics   ') AS trimmed_string;

Результат: Возвращает 'SQL Basics' без начальных и конечных пробелов.

В простейшем случае TRIM() убирает пробелы по краям строки. В ряде СУБД функция также позволяет явно указать, какие символы нужно удалить.

REPLACE() - Заменяет вхождения подстроки в строке.

Синтаксис:

REPLACE(string, old_substring, new_substring)

Пример:

SELECT REPLACE(phone_number, '-', '') AS cleaned_phone
FROM contacts;

Результат: Удаляет дефисы из значений phone_number.

INSTR() или CHARINDEX() - Находит позицию подстроки в строке.

Синтаксис:

INSTR(string, substring)       -- Для большинства баз данных
CHARINDEX(substring, string)   -- Для SQL Server

Пример:

SELECT INSTR(email, '@') AS at_position
FROM users;

Результат: Возвращает позицию символа @ в столбце email.

LEFT() и RIGHT() - Извлекает указанное количество символов из начала или конца строки.

Синтаксис:

LEFT(string, number_of_characters)
RIGHT(string, number_of_characters)

Пример:

SELECT LEFT(product_code, 3) AS product_prefix,
       RIGHT(product_code, 4) AS product_suffix
FROM products;

Результат: Извлекает первые 3 символа и последние 4 символа из product_code.

FORMAT() или TO_CHAR() - Форматирует строку или число в определенном формате.

Синтаксис:

FORMAT(value, format)       -- Для SQL Server
TO_CHAR(value, format)      -- Для Oracle

Пример:

SELECT FORMAT(salary, 'C') AS formatted_salary
FROM employees;

Результат: Форматирует столбец salary как валюту.

Практическое применение

1. Очистка данных: Используйте TRIM() и REPLACE() для очистки данных, например, удаления лишних пробелов или нежелательных символов.

2. Форматирование вывода: Используйте UPPER(), LOWER(), CONCAT() и CONCAT_WS() для стандартизации и форматирования текста в отчетах.

3. Извлечение информации: Используйте SUBSTRING(), LEFT() и RIGHT() для извлечения определенных частей строки, таких как префиксы или доменные имена.

4. Проверка данных: Используйте функции длины в символах (например, CHAR_LENGTH() или LEN()) и INSTR() для проверки структуры строк, например, длины телефонных номеров или наличия символа @ в адресах электронной почты.

Основные выводы из этого урока

Строковые функции — это важные инструменты для работы с текстовыми данными в SQL. Освоив эти функции, вы сможете очищать, форматировать и извлекать ценную информацию из ваших данных. Практикуйтесь с этими функциями в реальных сценариях, чтобы улучшить свои навыки работы с SQL.