ГлавнаяРазноеSql replace пример

Альтернатива PHP функции STR_REPLACE MySQL. Sql replace пример


REGEXP_REPLACE ФУНКЦИЯ — Oracle PL/SQL •MySQL •SQL Server

В этом учебном пособии вы узнаете, как использовать функцию REGEXP_REPLACE Oracle/PLSQL с синтаксисом и примерами.

Описание

Функция Oracle/PLSQL REGEXP_REPLACE является расширением функции REPLACE. Эта функция, введенная в Oracle 10g, позволит вам заменить последовательность символов в строке другим набором символов, используя сопоставление шаблонов регулярных выражений.

Синтаксис

Синтаксис функции Oracle/PLSQL REGEXP_REPLACE :

REGEXP_REPLACE( string, pattern [, replacement_string [, start_position [, nth_appearance [, match_parameter ] ] ] ] )

Параметры или аргументы

stringСтрока для поиска. Это могут быть CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB или NCLOB.

patternШаблон. Регулярное выражение для сопоставления. Это может быть комбинацией следующих значений:

Значение Описание
^ Соответствует началу строки. При использовании match_parameter с m, соответствует началу строки в любом месте в пределах выражения.
$ Соответствует концу строки. При использовании match_parameter с m, соответствует концу строки в любом месте в пределах выражения.
* Соответствует нолю или более вхождений.
+ Соответствует одному или более вхождений.
? Соответствует нолю или одному вхождению.
. Соответствует любому символу, кроме NULL.
| Используется как «OR», чтобы указать более одной альтернативы.
[ ] Используется для указания списка совпадений, где вы пытаетесь соответствовать любому из символов в списке.
[^ ] Используется для указания списка nonmatching, где вы пытаетесь соответствовать любому символу, за исключением тех кто в списке.
( ) Используется для групповых выражений в качестве подвыражений.
{m} Соответствует m раз.
{m,} Соответствие как минимум m раз.
{m,n} Соответствие как минимум m раз, но не более n раз.
\n n представляет собой число от 1 до 9. Соответствует n-му подвыражению находящемуся в ( ) перед \n.
[..] Соответствует одному сопоставлению элемента, который может быть более одного символа.
[::] Соответствует классу символов.
[==] Соответствует классу эквивалентности
\d Соответствует цифровому символу.
\D Соответствует не цифровому символу.
\w Соответствует текстовому символу.
\W Соответствует не текстовому символу.
\s Соответствует символу пробел.
\S Соответствует не символу пробел.
\A Соответствует началу строки или соответствует концу строки перед символом новой строки.
\Z Соответствует концу строки.
*? Соответствует предыдущему шаблону ноль или более вхождений.
+? Соответствует предыдущему шаблону один или более вхождений.
?? Соответствует предыдущему шаблону ноль или одному вхождению.
{n}? Соответствует предыдущему шаблону n раз.
{n,}? Соответствует предыдущему шаблону, по меньшей мере n раз.
{n,m}? Соответствует предыдущему шаблону, по меньшей мере n раз, но не более m раз.

replacement_stringНеобязательный. Соответствующие шаблоны в строке будут заменены на replace_string. Если параметр replacement_string опущен, то функция просто удаляет все совпадающие шаблоны и возвращает полученную строку.

start_positionНеобязательный. Это позиция в строке, откуда начнется поиск. Если этот параметр опущен, по умолчанию он равен 1, который является первой позицией в строке.

nth_appearanceНеобязательный. Это n-й вид шаблона в строке. Если этот параметр опущен, по умолчанию он равен 1, который является первым вхождением шаблона в строке. Если вы укажете 0 для этого параметра, все вхождения шаблона в строке будут заменены.

match_parameterНеобязательный. Это позволяет изменять поведение соответствия для условия REGEXP_REPLACE. Это может быть комбинацией следующих значений:

Значение Описание
‘c’ Выполняет чувствительное к регистру согласование.
‘i’ Выполняет не чувствительное к регистру согласование.
‘n’ Позволяет период символа (.) для соответствия символа новой строки. По умолчанию, период метасимволы.
‘m’ Выражение допускает, что есть несколько строк, где ^ это начало строки, а $ это конец строки, независимо от позиции этих символов в выражении. По умолчанию предполагается, что выражение в одной строке.
‘x’ Игнорируются символы пробелов. По умолчанию, символы пробелов совпадают, как и любой другой символ.

Функция REGEXP_REPLACE возвращает строковое значение.

Примечание

  • Если для параметра match_parameter имеются противоречивые значения, функция REGEXP_REPLACE будет использовать последнее значение.
  • См. также функцию REPLACE.

Применение

Функция REGEXP_REPLACE может использоваться в следующих версиях Oracle/PLSQL:

  • Oracle 12c, Oracle 11g, Oracle 10g

Пример совпадения с первым словом

Рассмотрим пример использования функции REGEXP_REPLACE для замены первого слова в строке.

Например:

SELECT REGEXP_REPLACE ('Bing is a great search engine.', '^(\S*)', 'Google') FROM dual; --Результат: 'Google is a great search engine.'

SELECT REGEXP_REPLACE ('Bing is a great search engine.', '^(\S*)', 'Google')

FROM dual;

--Результат: 'Google is a great search engine.'

Этот пример вернет ‘Google is a great search engine.’, потому что начало совпадения в начале строки, как указано символом ^, а затем найдет первое слово в соответствии с (\S*). Затем функция заменит это первое слово на ‘Google’.

Пример совпадения цифр

Рассмотрим пример, как мы будем использовать функцию REGEXP_REPLACE для сопоставления шаблону цифровых символов.

Например:

SELECT REGEXP_REPLACE ('1, 4, и 10 числа для примера.', '\d', '@') FROM dual; --Результат: @, @, и @@ числа для примера.

SELECT REGEXP_REPLACE ('1, 4, и 10 числа для примера.', '\d', '@')

FROM dual;

--Результат: @, @, и @@ числа для примера.

Этот пример заменит все числа в строке, как указано в шаблоне \d, на символ @.

Мы могли бы изменить наш шаблон для поиска только двухзначных чисел.

Например:

SELECT REGEXP_REPLACE ('1, 4, и 10 числа для примера', '(\d)(\d)', '@') FROM dual; --Результат: 1, 4, и @ числа для примера

SELECT REGEXP_REPLACE ('1, 4, и 10 числа для примера', '(\d)(\d)', '@')

FROM dual;

 

--Результат:  1, 4, и @ числа для примера

Этот пример заменит число, которое имеет две цифры, как указано в шаблоне (\d)(\d). В этом случае он пропустит числовые значения 2 и 5 и заменит 10 символом @.

Теперь рассмотрим, как мы будем использовать функцию REGEXP_REPLACE со столбцом таблицы для замены двухзначных чисел.Например:

SELECT REGEXP_REPLACE (address, '(\d)(\d)', 'Str') FROM contacts;

SELECT REGEXP_REPLACE (address, '(\d)(\d)', 'Str')

FROM contacts;

В этом примере мы заменим все двузначные значения поля address в таблице contacts на значение ‘Str’.

Пример сопоставления нескольких альтернатив.

Следующий пример, который мы рассмотрим, включает использование | шаблон. | шаблон используется как «ИЛИ», чтобы указать несколько альтернатив.

Например:

SELECT REGEXP_REPLACE ('AeroSmith', 'a|e|i|o|u', 'R') FROM dual; --Результат: ARrRSmRth

SELECT REGEXP_REPLACE ('AeroSmith', 'a|e|i|o|u', 'R')

FROM dual;

--Результат: ARrRSmRth

Этот пример вернет ‘ARrRSmRth’, потому что он ищет первую гласную (a, e, i, o или u) в строке. Поскольку мы не указали значение match_parameter, функция REGEXP_REPLACE будет выполнять поиск с учетом регистра, что означает, что ‘A’ в ‘AeroSmith’ не будет сопоставляться.

Мы могли бы изменить наш запрос, чтобы выполнить поиск без учета регистра следующим образом:

SELECT REGEXP_REPLACE ('AeroSmith', 'a|e|i|o|u', 'R', 1, 0, 'i') FROM dual; --Результат: RRrRSmRth

SELECT REGEXP_REPLACE ('AeroSmith', 'a|e|i|o|u', 'R', 1, 0, 'i')

FROM dual;

--Результат: RRrRSmRth

Теперь, поскольку мы указали match_parameter = ‘i’, запрос заменит ‘A’ в строке. На этот раз ‘A’ в ‘AeroSmith’ сопоставится с шаблоном. Заметим также, что мы указали 5-й параметр как 0, чтобы были заменены все вхождения.

Теперь рассмотри, как вы будете использовать эту функцию со столбцом.

Итак, допустим, у нас есть таблица contact со следующими данными:

contact_id last_name
1000 AeroSmith
2000 Joy
3000 Scorpions

Теперь давайте запустим следующий запрос:

SELECT contact_id, last_name, REGEXP_REPLACE (last_name, 'a|e|i|o|u', 'R', 1, 0, 'i') AS "New Name" FROM contacts;

SELECT contact_id, last_name, REGEXP_REPLACE (last_name, 'a|e|i|o|u', 'R', 1, 0, 'i') AS "New Name"

FROM contacts;

Запрос вернет следующие результаты:

contact_id last_name New Name
1000 AeroSmith RRrRSmRth
2000 Joy JRy
3000 Scorpions ScRrpRRns

Пример совпадений на основе параметра nth_occurrence

Следующий пример, который мы рассмотрим, включает параметр nth_occurrence. Параметр nth_occurrence позволяет вам выбрать, какое вхождение шаблона вы хотите заменить в строке.

Первое вхождение

Рассмотрим, как заменить первое вхождение шаблона в строке.

Например:

SELECT REGEXP_REPLACE ('Scorpions', 'a|e|i|o|u', 'Z', 1, 1, 'i') FROM dual; --Результат: ScZrpions

SELECT REGEXP_REPLACE ('Scorpions', 'a|e|i|o|u', 'Z', 1, 1, 'i')

FROM dual;

 

--Результат: ScZrpions

Этот пример заменит третий символ (‘e’) в ‘Scorpions’, потому что он заменяет первое вхождение гласного (a, e, i, o или u) в строке.

Второе вхождение

Затем мы выберем для второго вхождения шаблона в строку.

Например:

SELECT REGEXP_REPLACE ('Scorpions', 'a|e|i|o|u', 'Z', 1, 2, 'i') FROM dual; --Результат: ScorpZons

SELECT REGEXP_REPLACE ('Scorpions', 'a|e|i|o|u', 'Z', 1, 2, 'i')

FROM dual;

 

--Результат: ScorpZons

Этот пример заменит шестой символ (‘i’) в ‘Scorpions’, потому что он заменяет второе вхождение гласного (a, e, i, o или u) в строке.

Третье вхождение

Например:

SELECT REGEXP_REPLACE ('Scorpions', 'a|e|i|o|u', 'Z', 1, 3, 'i') FROM dual; --Результат: ScorpiZns

SELECT REGEXP_REPLACE ('Scorpions', 'a|e|i|o|u', 'Z', 1, 3, 'i')

FROM dual;

 

--Результат: ScorpiZns

Этот пример заменит седьмой символ (‘o’) в ‘Scorpions’, потому что он заменяет третье вхождение гласного (a, e, i, o или u) в строке.

oracleplsql.ru

sql - Функция SQL REPLACE, как заменить одну букву

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

replace(replace(cc.contype,'y','z'),'x','y') as ContractType,

Это преобразует все символы y в z, прежде чем трансформировать символы x в y.

Если вы используете более общее решение, вы можете делать объединенные запросы, например:

select 'Signed: ' || cc.contype as ContractType wherecc.contype like 'C%' from wherever union all select 'Estimate: ' || cc.contype as ContractType where cc.contype like 'E%' from wherever

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

Некоторые СУБД будут выполнять эти подзапросы параллельно с эффективностью.

Конечно, идеальным решением является изменение вашей схемы, чтобы вам не приходилось обрабатывать под-столбцы. Разделите столбец contype на два, сохранив первый символ в contype_first и contype_rest.

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

select contype_first || contype_rest ...

Для вашего текущего запроса вы можете использовать таблицу поиска:

lookup_table: first char(1) primary key description varchar(20)

содержащий:

first description ----- ----------- C Signed: E Estimate:

и запрос:

select lkp.description || cc.contype_rest from lookup_table lkp, real_table cc where lkp.first = cc.first ...

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

Даже если вы не можете заменить один столбец двумя независимыми столбцами, вы можете по крайней мере создать два новых и использовать триггер insert/update, чтобы синхронизировать их. Это дает вам старый способ и новый улучшенный способ доступа к информации contype.

И хотя это технически нарушает 3NF, что часто приемлемо по соображениям производительности, при условии, что вы понимаете и уменьшаете риски (с помощью триггеров).

qaru.site

regex - Шаблон Regex внутри функции SQL Replace?

Вы можете использовать PATINDEX для поиска первого индекса возникновения шаблона (строки). Затем используйте STUFF, чтобы наполнить другую строку в соответствие шаблону (строке).

Прокрутите каждую строку. Замените все незаконные символы тем, что вы хотите. В вашем случае замените не числовое значение пустым. Внутренний цикл - это если у вас более одного незаконного символа в текущей ячейке цикла.

DECLARE @counter int SET @counter = 0 WHILE(@counter < (SELECT MAX(ID_COLUMN) FROM Table)) BEGIN WHILE 1 = 1 BEGIN DECLARE @RetVal varchar(50) SET @RetVal = (SELECT Column = STUFF(Column, PATINDEX('%[^0-9.]%', Column),1, '') FROM Table WHERE ID_COLUMN = @counter) IF(@RetVal IS NOT NULL) UPDATE Table SET Column = @RetVal WHERE ID_COLUMN = @counter ELSE break END SET @counter = @counter + 1 END

Внимание! Это медленное! Наличие столбца varchar может повлиять. Поэтому использование LTRIM RTRIM может немного помочь. Несмотря на это, он медленный.

Кредит переходит на этот ответ на StackOverFlow.

ИЗМЕНИТЬ Кредит также отправляется на @srutzky

Изменить (by @Tmdean) Вместо того, чтобы делать одну строку за раз, этот ответ можно адаптировать к более основанному на множестве решения. Он по-прежнему выполняет итерацию максимального числа нечисловых символов в одной строке, поэтому он не идеален, но я думаю, что это должно быть приемлемым в большинстве ситуаций.

WHILE 1 = 1 BEGIN WITH q AS (SELECT ID_Column, PATINDEX('%[^0-9.]%', Column) AS n FROM Table) UPDATE Table SET Column = STUFF(Column, q.n, 1, '') FROM q WHERE Table.ID_Column = q.ID_Column AND q.n != 0; IF @@ROWCOUNT = 0 BREAK; END;

Вы также можете повысить эффективность довольно много, если вы поддерживаете бит столбца в таблице, который указывает, было ли поле еще очищено. (NULL представляет "Неизвестный" в моем примере и должен быть столбцом по умолчанию.)

DECLARE @done bit = 0; WHILE @done = 0 BEGIN WITH q AS (SELECT ID_Column, PATINDEX('%[^0-9.]%', Column) AS n FROM Table WHERE COALESCE(Scrubbed_Column, 0) = 0) UPDATE Table SET Column = STUFF(Column, q.n, 1, ''), Scrubbed_Column = 0 FROM q WHERE Table.ID_Column = q.ID_Column AND q.n != 0; IF @@ROWCOUNT = 0 SET @done = 1; -- if Scrubbed_Column is still NULL, then the PATINDEX -- must have given 0 UPDATE table SET Scrubbed_Column = CASE WHEN Scrubbed_Column IS NULL THEN 1 ELSE NULLIF(Scrubbed_Column, 0) END; END;

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

qaru.site

Альтернатива PHP функции STR_REPLACE MySQL

Привет. Сегодня мы рассмотрим как же можно заменить подстроку на другую подстроку в ячейке таблицы БД на MySQL или попросту посмотрим на альтернативу php функции STR_REPLACE в MySQL.

MySQL аналог STR_REPLACE MySQL

В MySQL есть прекрасный аналог, который работает прямо в запросах к БД.Выглядит он как:

REPLACE(haystack, needle, replace)

Соответственно, здесь: haystack (стог сена) — графа/колонка/данные где нужно искать строку для замены, needle — строка, которую нужно заменить, replace — строка на которую нужно осуществить замену needle.

Эту MySQL функцию можно использовать непосредственно в запросах UPDATE. Представляете насколько упростит жизнь использование этой функции, если нужно где-нибудь что-то заменить, а записей 2000000, и на PHP писать str_replace не хочется, да и долго будет работать.

Рассмотрим на примере STR_REPLACE MySQL

Всё достаточно просто, предположим, у нас есть таблица, в которой в каждой ячейке графы «Name» нужно заменить «Иван» на «Ваня»:

UPDATE my_table SET name=REPLACE(name, 'Иван', 'Ваня')

Или, например, усложним пример, и сделаем замены «Иван» на «Ваня», только для тех, у кого возраст (графа Age) меньше 18 лет (то есть не совершеннолетним):

UPDATE my_table SET name=REPLACE(name, 'Иван', 'Ваня') WHERE age

Теперь не нужно делать множество запросов на PHP, всё выполнится за считанные милисекунды (в зависимости от размера таблицы БД).

Полная документация по функции REPLACE (STR_REPLACE MySQL) доступна по адресу: REPLACE MySQL.

Надеюсь, эта статья поможет вам облегчить решение задачи по замене каких-либо данных в базах данных на MySQL.

53

Понравилась или помогла статья? Купите мне кофе

htmler.ru