REPLACE: примеры (SQL)
REPLACE(string_expression, string_pattern, string_replacement): stringОписание функции REPLACE в MS SQL Server
Функция REPLACE в Microsoft SQL Server предназначена для замены всех вхождений указанной подстроки в исходной строке на другую подстроку. Это строковая функция, которая часто используется для очистки данных, преобразования форматов или манипуляций с текстом.
Функция применяется, когда требуется модифицировать текстовые данные в столбцах таблиц, переменных или литералах, выполняя глобальную замену одного шаблона на другой.
Синтаксис:REPLACE ( input_string, substring_to_find, substring_to_replace )
- input_string (
string_expression): Исходная строка, в которой выполняется поиск и замена. Может быть символьного или двоичного типа данных. - substring_to_find (
string_expression): Подстрока, которую необходимо найти в исходной строке. Если подстрока пустая (''), функция возвращает исходную строку. - substring_to_replace (
string_expression): Подстрока, на которую заменяются все найденные вхожденияsubstring_to_find.
Возвращаемое значение: Функция возвращает строку (varchar или nvarchar), в которой все вхождения substring_to_find заменены на substring_to_replace. Тип возвращаемых данных определяется типами входных аргументов. Если любой из аргументов имеет тип nvarchar, возвращается nvarchar, иначе — varchar. Если какой-либо аргумент имеет значение NULL, функция возвращает NULL.
Основные примеры использования REPLACE
Простая замена текста:
SELECT REPLACE('Hello World', 'World', 'SQL') AS Result;Result ------------ Hello SQL
Замена нескольких вхождений:
SELECT REPLACE('abc abc def abc', 'abc', 'xyz') AS Result;Result ------------------- xyz xyz def xyz
Удаление подстроки (замена на пустую строку):
SELECT REPLACE('Телефон: +7 (123) 456-78-90', '-', '') AS Result;Result ------------------------------ Телефон: +7 (123) 4567890
Работа с NULL:
SELECT REPLACE(NULL, 'a', 'b') AS Result1,
REPLACE('abc', NULL, 'b') AS Result2,
REPLACE('abc', 'a', NULL) AS Result3;Result1 Result2 Result3 ------- ------- ------- NULL NULL NULL
Замена с учетом регистра: Функция чувствительна к регистру в зависимости от параметров сортировки (collation) базы данных или столбца.
SELECT REPLACE('Hello World HELLO', 'hello', 'Hi') AS Result;Result ------------------- Hello World HELLO
Похожие функции в MS SQL Server
- STUFF: Удаляет указанную длину символов в одном месте строки и вставляет другую строку в это место. Полезно для замены подстроки в конкретной позиции, а не всех вхождений.
STUFF('abcdef', 2, 3, 'xyz')вернет 'axyzef'. - PATINDEX и SUBSTRING: Комбинация этих функций позволяет находить и заменять подстроки по сложному шаблону (регулярным выражениям, которые в простом виде поддерживаются через
%и_), но требует больше кода для реализации полной замены. - TRANSLATE (доступна с SQL Server 2017): Заменяет каждый отдельный символ из набора символов на соответствующий символ из другого набора. Эффективна для множественных замен одиночных символов, например, преобразования кавычек или скобок.
TRANSLATE('(123)', '()', '[]')вернет '[123]'.
Когда что использовать: REPLACE применяется для простой и глобальной замены строк. STUFF удобна для замены в конкретной позиции. TRANSLATE оптимальна для замены наборов одиночных символов. Для замены по сложным шаблонам потребуется комбинация других строковых функций или CLR-интеграция.
Распространенные ошибки при работе с REPLACE
1. Путаница с порядком аргументов: Перепутанные второй и третий аргументы ведут к неожиданным результатам.
-- Ошибка: Вместо удаления 'abc' происходит замена 'xyz' на 'abc'
SELECT REPLACE('test abc xyz', 'xyz', 'abc') AS Wrong,
REPLACE('test abc xyz', 'abc', '') AS Correct;Wrong Correct ------------- ---------- test abc abc test xyz
2. Неучет чувствительности к регистру: Поведение зависит от collation. При несовпадении регистра замена может не произойти.
-- Если collation чувствительно к регистру
SELECT REPLACE('SQL Server', 'sql', 'My') AS Result;Result ---------- SQL Server
3. Ожидание замены только первого вхождения: Функция всегда заменяет все вхождения.
SELECT REPLACE('a;b;c;', ';', ', ') AS Result; -- Заменяет все точки с запятойResult ----------- a, b, c,
4. Попытка замены NULL: Если исходная строка NULL, результат всегда NULL, даже если заменяемая подстрока тоже NULL.
История изменений функции
В MS SQL Server функция REPLACE присутствует с ранних версий, и ее основное поведение оставалось стабильным. Существенных изменений в синтаксисе или базовой логике не было.
Косвенные улучшения связаны с общим развитием платформы:
- С увеличением максимального размера типов данных (
varchar(max),nvarchar(max)), начиная с SQL Server 2005, функция может обрабатывать более длинные строки. - Производительность функции могла оптимизироваться в новых версиях ядра СУБД.
- Введение функции
TRANSLATEв SQL Server 2017 предоставило альтернативу для сценариев замены одиночных символов, но не затронуло самуREPLACE.
Таким образом, код с использованием REPLACE, написанный для старых версий SQL Server (например, 2000), будет корректно работать и в актуальных версиях.
Расширенные примеры применения
1. Рекурсивная замена для удаления двойных пробелов:
DECLARE @Text NVARCHAR(100) = 'Текст с многими пробелами.';
WHILE CHARINDEX(' ', @Text) > 0
SET @Text = REPLACE(@Text, ' ', ' ');
SELECT @Text AS Result;Result -------------------------------- Текст с многими пробелами.
2. Массовое обновление данных в таблице:
-- Замена формата телефонного номера в столбце
UPDATE Clients
SET PhoneNumber = REPLACE(PhoneNumber, '+7 ', '8')
WHERE PhoneNumber LIKE '+7%';3. Разбор строки, содержащей разделители:
-- Подготовка строки для разбивки с помощью STRING_SPLIT
SELECT value AS Item
FROM STRING_SPLIT(REPLACE('яблоко,апельсин, банан', ' ', ''), ',');Item --------- яблоко апельсин банан
4. Эмуляция функции TRIM для старых версий SQL Server:
DECLARE @str VARCHAR(100) = ' текст с пробелами ';
SELECT REPLACE(REPLACE(REPLACE(@str, ' ', '^&'), '&^', ''), '^&', ' ') AS Trimmed; -- Сложный способ
-- Проще с LTRIM и RTRIM
SELECT LTRIM(RTRIM(@str)) AS TrimmedSimple;5. Замена с использованием вложенного вызова для преобразования шаблона:
-- Преобразование даты из формата 'DD.MM.YYYY' в 'YYYY-MM-DD'
SELECT REPLACE(REPLACE('31.12.2023', '.', '-'),
SUBSTRING('31.12.2023', 4, 6),
CONCAT('-', SUBSTRING('31.12.2023', 7,4), '-',
SUBSTRING('31.12.2023', 1,2))) AS ConvertedDate;
-- Более надежный способ с PARSE и CONVERT
SELECT CONVERT(VARCHAR, PARSE('31.12.2023' AS DATE USING 'ru-RU'), 23);6. Обработка JSON-подобных данных (простая замена кавычек):
SELECT REPLACE('{"name": "Ivan", "age": 30}', '"', '''') AS SQL_Ready_String;SQL_Ready_String
------------------------------------
{'name': 'Ivan', 'age': 30}7. Подсчет количества вхождений подстроки: Используя разницу в длине строк.
DECLARE @str VARCHAR(100) = 'abaababa';
DECLARE @sub VARCHAR(10) = 'ab';
SELECT (LEN(@str) - LEN(REPLACE(@str, @sub, ''))) / LEN(@sub) AS CountOccurrences;CountOccurrences ---------------- 3
Аналоги функции в других СУБД и языках
MySQL: Функция REPLACE аналогична по синтаксису и поведению.
-- MySQL
SELECT REPLACE('MySQL Database', 'My', 'Our');OurSQL Database
PostgreSQL: Используется функция REPLACE с аналогичным синтаксисом. Также существует функция REGEXP_REPLACE для замены по регулярным выражениям.
-- PostgreSQL
SELECT REPLACE('PostgreSQL', 'SQL', 'DB');PostgreDB
Oracle: Функция REPLACE ведет себя аналогично. Дополнительно Oracle предоставляет REGEXP_REPLACE для сложных шаблонов.
-- Oracle
SELECT REPLACE('Oracle PL/SQL', 'PL/', '') FROM dual;Oracle SQL
SQLite: Поддерживает функцию REPLACE с тем же поведением.
-- SQLite
SELECT REPLACE('SQLite', 'SQL', 'R');RLite
В языках программирования: В Python это метод str.replace(), в JavaScript — метод String.prototype.replace() (но он по умолчанию заменяет только первое вхождение, для глобальной замены нужен флаг /g с регулярным выражением). В C# — метод String.Replace().