Oracle sql изменить строку

Обновлено: 05.07.2024

Продолжим разбор функций PL/SQL. Символьные функции далее: RPAD(строка 1, х, строка 2) Вот еще интересная функция! :) Возвращает "строка 1", дополненную справа до размера х символами "строка 2". Если размер "строка 2" меньше х, то при необходимости она дублируется. Если размер "строка 2" больше х то берутся только первые х ее символов. Если "строка 2" не указана, то ее заменяют символы пробела. Пример, строка 5*, 6*. Обратите внимание, что х указывается как размер строки символов, отображаемой на экране, а не как реальный размер. Пример 14*, 15*. RTRIM(строка 1, строка 2) Возвращает "строка 1", в которой удалены крайние правые символы, идентичные символам "строка 2". Значением по умолчанию для "строка 2", является знак пробела. "строка 1" просматривается с левого края, и при встрече первого символа не совпадающего с "строка 2", возвращается результат. Пример строка 16*, 17*, 18*, 19*. SUBSTR(строка 1, a, [,b]) Вот с этой функцией, я наиболее часто работал, очень удобная штучка! Возвращает часть "строка 1", начинающуюся с символа с номером a, и имеющую длину b символов. Если a = 0, это равносильно тому, что a = 1 (начало строки) если b положительно возвращаются символы слева направо. Если b отрицательно то, начиная с конца строки и считаются справа налево! Если b отсутствует, то по умолчанию возвращаются все символы, до конца строки. Если b меньше 1, то возвращается значение NULL. Если в качестве a и b, указано число с плавающей точкой, его дробная часть отбрасывается! Пример строка 20*, 21*, 22*. TRANSLATE(строка_символов, заменяемая_трока, вносимая_строка) Возвращает "строка_символов", в которой все вхождения каждого символа "заменяемая_трока" замещаются соответствующим символом "вносимая_строка". Функция TRANSLATE, является расширением функции REPLACE. Если "заменяемая_трока" длиннее чем "вносимая_строка", все ее лишние символы удаляются поскольку для них нет соответствующих символов во "вносимая_строка". "вносимая_строка" не может быть пустой. Oracle интерпретирует пустую строку как значение NULL, а если любой аргумент функции TRANSLATE является NULL, то результат тоже будет NULL. Пример строка 23*, 24*. UPPER(строка) Возвращает "строка", в которой все символы прописные. Символы не являющиеся буквами не изменяются. Пример, строка 4*. Пример строка 25*.

Вот, с символьными функциями возвращающими символьные значения пока все. Далее продолжим, следующую группу функций PL/SQL. Пробуйте!

Мы продолжаем изучение языка запросов SQL, и сегодня мы с Вами будем разговаривать о строковых функциях SQL. Мы рассмотрим основные и часто используемые строковые функции, такие как: LOWER, LTRIM, REPLACE и другие, все рассматривать мы будем, конечно же, на примерах.

В прошлой статье «Основы языка запросов SQL – оператор SELECT» мы с Вами узнали, что такое вообще SQL, а также рассмотрели основы оператора SELECT (условия отбора, агрегатные функции), здесь же мы продолжаем изучение оператора SELECT, а если говорить конкретней, то будем рассматривать строковые функции SQL.

Строковые функции используются для возвращения уже отредактированного значения. Другими словами, например, у Вас в колонке все значения состоят более чем из 20 символов, а Вам нужно возвратить всего первые 5 символов, это можно сделать с помощью так называемых строковых функциях.

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

Функция CONCAT

Начнем с функции CONCAT – она используется для объединения значений двух столбцов в один. Допустим у Вас в таблице, «Имя» и «Фамилия» находятся в разных колонках (что логично), а Вам, для какого-нибудь отчета, нужно чтобы они выводились в одной колонке. Вы можете легко использовать данную функцию.

С помощью данного запроса Вы объедините две колонки в одну, т.е. у Вас получится не вот так

Иван	Иванов
Петр	Петров

Иван Иванов

Петр Петров

Аналогично этому запросу можно использовать следующую конструкцию (применимо в PostgreSQL).

Или чтобы отделить пробелом введите

т.е. две вертикальные черты объединяют два столбца в один, а чтобы отделить их пробелом я поставил между ними пробел (можно использовать любой символ, например тире или двоеточие) в апострофах и объединил также двумя вертикальными чертами (в Transact-SQL вместо двух вертикальных черточек используется знак +).

Функция INITCAP

Дальше идет также очень полезная функция, INITCAP – которая возвращает значение в строке, в которой каждое слово начинается с заглавной буквы, а продолжается маленькими. Это нужно для того, если у Вас в той или иной колонке не соблюдают правила заполнения и для того чтобы вывести все это дело в красивом виде можно использовать данную функцию, например, у Вас в таблице записи в колонке name следующего вида: ИВАН иванов или петр петров, Вы применяете данную функцию.

И у Вас получится вот так.

Иван Иванов

Петр Петров

Функция LOWER

Следующая функция также может пригодиться. LOWER – возвращает в значение все слова с маленькой буквы. Используем пример выше, но только будем пользоваться следующий функцией.

А теперь получится вот так.

иван иванов

петр петров

Функция UPPER

Похожая функция, только возвращает все символы с заглавной буквы, это UPPER.

ИВАН ИВАНОВ

ПЕТР ПЕТРОВ

Функция LPAD

name – название колонки;
20 – количество знаков (длина поля);
‘-‘ – символ, которым нужно дополнить до необходимого количества знаков.

Функция RPAD

Сразу рассмотрим обратную функцию. RPAD – действие и синтаксис тот же что и у LPAD, только дополняются символы справа (в LPAD слева).

Функция LTRIM

Далее идет тоже в некоторых случаях полезная функция, LTRIM – эта функция удаляет крайние левые символы, которые Вы укажите. Например, у Вас в базе есть колонка «город», в которой город указан в виде «г.Москва», а также есть города которые указанны в виде просто «Москва». Но Вам нужно вывести отчет только в виде «Москва» без «г.», но как это сделать, если есть и такие и такие? Вы просто указываете своего рода шаблон «г.» и если крайние левые символы начинаются с «г.», то эти символы просто не будут выводиться.

Данная функция просматривает символы слева, если символов по шаблону нет в начале строки, то она возвращает исходное значение ячейки, а если есть, то удаляет их.

Функция RTRIM

Также давайте сразу рассмотрим обратную функцию. RTRIM – то же самое что и LTRIM только символы ищутся справа.

Примечание! В Transact-SQL функции RTRIM и LTRIM удаляют пробелы справа и слева соответственно.

Функция REPLACE

Теперь рассмотрим такую интересную функцию как REPLACE – она возвращает строку, в которой все совпадения символов, заменяются на Ваши символы, которые Вы укажите. Для чего ее можно использовать, например, у Вас в базе есть колонки, в которых встречаются некие разделительные символы, допустим «/». Например, Иван/Иванов, а Вам хотелось бы вывести Иван-Иванов, то напишите

и у Вас произойдет замена символов.

Функция TRANSLATE

TRANSLATE – строковая функция, которая заменяет все символы в строке, на те символы, которые Вы укажите. Исходя из названия функции, можно догадаться, что это полный перевод строки. Отличие данной функции от REPLACE в том, что она заменяет каждый символ, который Вы укажите, т.е. у Вас есть три символа, допустим абв и с помощью TRANSLATE Вы его можете заменить на abc таким образом у Вас а=a, б=b, в=c и по такому принципу будут заменяться все совпадения символов. А если Вы заменяли с помощью REPLACE, то у Вас искалось только полное совпадение символов абв расположенных подряд.

Функция SUBSTR

SUBSTR – данная функция, возвращает только тот диапазон символов, который Вы укажите. Другими словами, допустим, строка из 10 символов, а Вам все десять не нужны, а допустим, нужны только 3-8 (с третьего по восьмой). С помощью данной функции Вы легко можете это сделать. Например, у Вас в базе есть какой-нибудь идентификатор, фиксированной длинны (типа: AA-BB-55-66-CC) и каждая комбинация символов что-то означает. И в один прекрасный момент Вам сказали вывести только 2 и 3 комбинацию символов, для этого вы пишите запрос следующего вида.

т.е. мы выводим все символы, начиная с 4 и заканчивая 8, и после этого запроса у Вас выведется вот это:

BB-55

Функция LENGTH – длина строки

Следующая функция также может пригодиться, это LENGTH – которая просто на всего считает количество символов в строке. Например, Вам нужно узнать, сколько символов в каждой ячейки столбца допустим «name», таблица следующего вида.

Иван

Сергей

Виталий

после этого запроса Вы получите вот это.

Заметка! Для комплексного изучения языка SQL рекомендую почитать мою книгу «SQL код», в ней язык SQL рассматривается как стандарт, чтобы после прочтения данной книги можно было работать с языком SQL в любой системе управления базами данных.

Вот мы с Вами и рассмотрели основные строковые функции SQL. В следующих статьях мы продолжим изучение SQL.

Передо мной частенько возникают задачи: удалить часть текста из текстовых полей базы данных, объединить строковые данные или еще что-нибудь связанное с текстом. Делать все это через админские панели сайтов очень неудобно и муторно. Гораздо проще бывает написать запрос к базе данных выполняющий все эти действия за пару секунд.

Символьные функции в языке sql

Начнем по порядку с самого простого. Первой рассмотрим строковую функцию ASCII, которая используется для определения ASCII-кода текстовых символов:

integer ASCII(str string)

Функция возвращает целое значение — ASCII-код первого левого символа строки str. В случае если строка str пустая возвращает 0 и NULL если строка str не существует.

Далее функция ORD, которая также определяет ASCII- код символов, но может обрабатывать также многобайтовые символы:

integer ORD(str string)

Если первый левый символ строки str многобайтовый, то возвращает его код в формате: ((первый байт ASCII- код)*256+(второй байт ASCII -код))[*256+третий байт ASCII -код. ]. В случае если первый левый символ строки str не является многобайтовым, работает как функция ASCII — возвращает его ASCII-код.

Функция CHAR, тесно связанная с функцией ASCII и выполняет обратное действие:

string CHAR(int integer, . )

Функция CHAR возвращает строку символов по их ASCII-кодам. Если среди значений встречается значение NULL, то оно пропускается.

SQL функции для объединения строк

Одна из самых популярных категорий функций. Ведь частенько бывает нужно объединить значения нескольких полей таблиц базы данных сайта. В языке SQL есть сразу несколько функций для конкатенации строк.

string CONCAT(str1 string, str2 string. )

Функция возвращает строку, созданную путем объединения аргументов. Можно указывать более двух аргументов. Если один из аргументов является NULL, то и возвращаемый результат будет NULL. Числовые значения преобразуются в строку.

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

Для таких случаев существует функция CONCAT_WS:

string CONCAT_WS(separator string, str1 string, str2 string. )

Функция объединяет строки как и функция CONCAT, но вставляет между аргументами разделитель separator. В случае если аргумент separator является NULL, то и результат будет NULL. Аргументы строки равные NULL пропускаются.

В случае объединения большого количества строк, которые необходимо отделять разделителем, функция CONCAT_WS гораздо удобнее функции CONCAT.

Иногда бывает необходимо удлинить строку до определенного количества символов за счет повторения какого-либо символа. Это тоже своего рода объединение строк. Для этого можно использовать функции LPAD и RPAD. Функции имеют следующий синтаксис:

string LPAD(str string, len integer, padstr string)
string RPAD(str string, len integer, padstr string)

Функция LPAD возвращает строку str дополненную слева строкой padstr до длины len. Функция RPAD выполняет тоже самое, только удлинение происходит с правой стороны.

В данных функциях необходимо обратить внимание на параметр len, который ограничивает количество выводимых символов. Поэтому если длина строки str будет больше чем параметр len, то строка будет обрезана:

Определение длины строки в sql запросах

Для определения количества символов в строке в языке SQL отвечает функция LENGTH — длина строки:

integer LENGTH(str string)

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

В случае использования многобайтовых кодировок функция LENGTH выдает не правильный результат. Например в случае если задана кодировка unicode, то запрос:

вернет 8. Что, легко заметить, в два раза больше реального количества символов. В этом случае нужно использовать функцию CHAR_LENGTH:

integer CHAR_LENGTH(str string)

Функция также возвращает длину строки str и поддерживает многобайтовые символы.

Поиск подстроки в строке средствами sql

Для вычисления позиции подстроки в строке в языке sql существует несколько функций. Первая, которую мы рассмотрим, функция POSITION:

integer POSITION(substr string IN str string)

Возвращает номер позиции первого вхождения подстроки substr в строке str и возвращает 0 если подстрока не найдена. Функция POSITION может работать с многобайтовыми символами.

Следующая функция LOCATE позволяет начинать поиск подстроки с определенной позиции:

integer LOCATE(substr string, str string, pos integer)

Возвращает позицию первого вхождения подстроки substr в строке str, начиная с позиции pos. Если параметр pos не задан, то поиск осуществляется с начала строки. Если подстрока substr не найдена, то возвращает 0. Поддерживает многобайтовые символы.

Аналогом функций POSITION и LOCATE является функция INSTR:

integer INSTR(str string, substr string)

Также как и функции выше возвращает позицию первого вхождения подстроки substr в строке str. Единственное отличие от функций POSITION и LOCATE то, что аргументы поменяны местами.

Далее рассмотрим функции, которые помогают получить подстроку.

Первыми рассмотрим сразу две функции LEFT и RIGHT, которые похожи по своему действию:

string LEFT(str string, len integer)
string RIGHT(str string, len integer)

Функция LEFT возвращает len первых символов из строки str, а функция RIGHT столько же последних. Поддерживают многобайтовые символы.

Далее рассмотрим одинаковые по итоговому результату функции SUBSTRING и MID:

string SUBSTRING(str string, pos integer, len integer)
string MID(str string, pos integer, len integer)

Функции позволяют получить подстроку строки str длиною len символов с позиции pos. В случае если параметр len не задан, то возвращается вся подстрока начиная с позиции pos.

Примеры с функцией MID не привожу, потому что результаты будут аналогичные.

Интересная функция SUBSTRING_INDEX:

string SUBSTRING_INDEX(str string, delim string, count integer)

Функция возвращает подстроку строки str, полученную путем удаления символов, идущих после разделителя delim, находящимся в позиции count. Параметр count может быть как положительным, так отрицательным. Если count положительный, то отсчет позиции разделителя будет вестись слева и удаляться будут символы находящиеся справа от разделителя. Если count отрицательный, то отсчет позиции разделителя ведется справа и удаляются символы находящиеся слева от разделителя. Возможно, описание получилось слишком запутанным, но на примерах станет понятней.

Здесь функция ищет второе вхождение точки, удаляет все символы справа от нее и возвращает получившуюся подстроку. И еще один пример с отрицательным значением параметра count:

В этом примере функция SUBSTRING_INDEX ищет вторую точку, отсчитывая позицию справа, удаляет символы слева от нее и выдает полученную подстроку.

Удаление пробелов из строки

Для удаления лишних пробелов из начала и конца строки в языке SQL есть три функции.

string LTRIM(str string)

Удаляет с начала строки str пробелы и возвращает результат.

string RTRIM(str string)

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

И третья функция TRIM позволяет сразу удалять пробелы из начала и из конца строки:

string TRIM([[BOTH | LEADING | TRAILING] [remstr] string FROM] str string)

Параметр str обязательный, остальные параметры не обязательные. В случае если задан только один параметр str, то возвращает строку str удалив пробелы из начала и конца строки одновременно.

С помощью пара метра remstr можно задавать символы или подстроки, которые будут удаляться из начала и конца строки. С помощью управляющих параметров BOTH, LEADING, TRAILING можно задавать откуда будут удаляться символы:

BOTH — удаляет подстроку remstr с начала и с конца строки;
LEADING — удаляет remstr с начала строки;
TRAILING — удаляет remstr с конца строки.

Функция SPACE позволяет получить строку состоящую из определенного количества пробелов:

string SPACE(n integer)

Возвращает строку, которая состоит из n пробелов.

Функция REPLACE нужна для замены заданных символов в строке:

string REPLACE(str string, from_str string, to_str string)

Функция заменяет в строке str все подстроки from_str на to_str и возвращает результат. Поддерживает многобайтные символы.

string REPEAT(str string, count integer)

Функция возвращает строку, которая состоит из count повторений строки str. Поддерживает многобайтовые символы.

Функция REVERSE переворачивает строку:

string REVERSE(str string)

Переставляет в строке str все символы с последнего на первый и возвращает результат. Поддерживает многобайтовые символы.

Функция INSERT для вставки подстроки в строку:

string INSERT(str string, pos integer, len integer, newstr string)

Возвращает строку полученную в результате вставки в строку str подстроки newstr с позиции pos. Параметр len указывает сколько символов будет удалено из строки str, начиная с позиции pos. Поддерживает многобайтовые символы.

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

string LCASE(str string) и string LOWER(str string)

Обе функции заменяют в строке str заглавные буквы на прописные и возвращают результат. И та и другая поддерживают многобайтовые символы.

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

string UCASE(str string) и string UPPER (str string)

Функции возвращают строку str, заменив все прописные символы на заглавные. Также поддерживают многобайтовые символы.
Пример:

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

В Oracle10g в области работы со строками произошли очень серьезные изменения: была реализована поддержка регулярных выражений. Причем речь идет не об упрощенной поддержке регулярных выражений вроде предиката LIKE, которая встречается в других СУБД. Компания Oracle предоставила в наше распоряжение отлично проработанный, мощный набор функций — то, что было необходимо в PL/SQL.

Проверка наличия совпадения

Регулярные выражения используются для описания текста, который требуется найти в строке (и возможно, подвергнуть дополнительной обработке). Давайте вернемся к примеру, который приводился ранее в этом блоге:

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

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

Здесь исходная_строка — символьная строка, в которой ищутся совпадения; шаблон — регулярное выражение, совпадения которого ищутся в исходной_строке; модификаторы — один или несколько модификаторов, управляющих процессом поиска. Если функция REGEXP_LIKE находит совпадение шаблона в исходной_строке , она возвращает логическое значение TRUE ; в противном случае возвращается FALSE .

Процесс построения регулярного выражения выглядел примерно так:

[a-z A-Z] Каждый элемент списка имен может состоять только из букв и пробелов. Квадратные скобки определяют набор символов, которые могут входить в совпадение. Диапазон a–z описывает все буквы нижнего регистра, а диапазон A–Z — все буквы верхнего регистра. Пробел находится между двумя компонентами выражения. Таким образом, этот шаблон описывает один любой символ нижнего или верхнего регистра или пробел.
[a-z A-Z] * Звездочка является квантификатором — служебным символом, который указывает, что каждый элемент списка содержит ноль или более повторений совпадения, описанного шаблоном в квадратных скобках.
[a-z A-Z] *, Каждый элемент списка должен завершаться запятой. Последний элемент является исключением, но пока мы не будем обращать внимания на эту подробность.
([a-z A-Z] *,) Круглые скобки определяют подвыражение, которое описывает некоторое количество символов, завершаемых запятой. Мы определяем это подвыражение, потому что оно должно повторяться при поиске.
([a-z A-Z ]*,)+ Знак + — еще один квантификатор, применяемый к предшествующему элементу (то есть к подвыражению в круглых скобках). В отличие от * знак + означает «одно или более повторений». Список, разделенный запятыми, состоит из одного или нескольких повторений подвыражения.
( [a-z A-Z]*,)+([a-z A-Z]*) В шаблон добавляется еще одно подвыражение: ( [a-z A-Z] *). Оно почти совпадает с первым, но не содержит запятой. Последний элемент списка не завершается запятой.
([a-z A-Z]*,)+([a-z A-Z]*) Мы добавляем квантификатор , чтобы разрешить вхождение ровно одного элемента списка без завершающей запятой.
^ ([a-z A-Z]*,)+([a-z A-Z]*)$ Наконец, метасимволы ^ и $ привязывают потенциальное совпадение к началу и концу целевой строки. Это означает, что совпадением шаблона может быть только вся строка вместо некоторого подмножества ее символов.

Функция REGEXP_LIKE анализирует список имен и проверяет, соответствует ли он шаблону. Эта функция оптимизирована для простого обнаружения совпадения шаблона в строке, но другие функции способны на большее!

Поиск совпадения

Функция REGEXP_INSTR используется для поиска совпадений шаблона в строке. Общий синтаксис REGEXP_INSTR :

Здесь исходная_строка — строка, в которой выполняется поиск; шаблон — регулярное выражение, совпадение которого ищется в исходной_строке; начальная_позиция — позиция, с которой начинается поиск; номер — порядковый номер совпадения (1 = первое, 2 = второе и т. д.); флаг_возвращаемого_значения — 0 для начальной позиции или 1 для конечной позиции совпадения; модификаторы — один или несколько модификаторов, управляющих процессом поиска (например, i для поиска без учета регистра). Начиная с Oracle11g, также можно задать параметр подвыражение (1 = первое, 2 = второе и т. д.), чтобы функция REGEXP_INST возвращала начальную позицию заданного подвыражения (части шаблона, заключенной в круглые скобки).

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

При выполнении этого фрагмента выясняется, что имя на букву A, завершающееся согласной (Andrew), начинается в позиции 22. А вот как проходило построение шаблона:

A Совпадение начинается с буквы A. Беспокоиться о запятых не нужно — на этой стадии мы уже знаем, что работаем со списком, разделенным запятыми.
A[a-z ]* За буквой A следует некоторое количество букв или пробелов. Квантификатор * указывает, что за буквой A следует ноль или более таких символов.
A[a-z ]*[^aeiou] В выражение включается компонент [ ^aeiou ], чтобы имя могло заканчиваться любым символом, кроме гласной. Знак ^ инвертирует содержимое квадратных скобок —
совпадает любой символ, кроме гласной буквы. Так как квантификатор не указан, требуется присутствие ровно одного такого символа.
A[a-z ]*[^aeiou], Совпадение должно завершаться запятой; в противном случае шаблон найдет совпадение для подстроки «An» в имени «Anna». Хотя добавление запятой решает эту проблему, тут же возникает другая: шаблон не найдет совпадение для имени «Aaron» в конце строки.
A[a-z ]*[^aeiou],|A[a-z ]*[^aeiou]$ В выражении появляется вертикальная черта (|), обозначение альтернативы: общее совпадение находится при совпадении любого из вариантов. Первый вариант завершается запятой, второй — нет. Второй вариант учитывает возможность того, что текущее имя стоит на последнем месте в списке, поэтому он привязывается к концу строки метасимволом $ .

Регулярные выражения — далеко не простая тема! Новички часто сталкиваются с нюансами обработки регулярных выражений, которые часто преподносят неприятные сюрпризы. Я потратил некоторое время на работу над этим примером и несколько раз зашел в тупик, прежде чем выйти на правильный путь. Не отчаивайтесь — с опытом писать регулярные выражения становится проще.

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

Получение текста совпадения

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

С подобными нежесткими схемами записи легко работать при помощи регулярных выражений, но очень трудно без них. Мы воспользуемся функцией REGEXP_SUBSTR для извлечения телефонного номера из строки с контактными данными:

Результат работы программы:

Ого! Шаблон получился довольно устрашающим. Давайте разобьем его на составляющие:

\(? Шаблон начинается с необязательного символа открывающей круглой скобки. Так как круглые скобки в языке регулярных выражений являются метасимволами (то есть имеют специальное значение), для использования в качестве литерала символ круглой скобки необходимо экранировать ( escape ), поставив перед ним символ \ (обратная косая черта). Вопросительный знак — квантификатор, обозначающий ноль или одно вхождение предшествующего символа. Таким образом, эта часть выражения описывает необязательный символ открывающей круглой скобки.
\d \d — один из операторов, появившихся в Oracle10g Release 2 под влиянием языка Perl. Он обозначает произвольную цифру. Квантификатор <> указывает, что предшествующий символ входит в шаблон заданное количество раз (в данном случае три). Эта часть шаблона описывает три цифры.
\)? Необязательный символ закрывающей круглой скобки.
[[:space:]\.\-]? В квадратных скобках перечисляются символы, для которых обнаруживается совпадение — в данном случае это пропуск, точка или дефис. Конструкция [ :space: ] обозначает символьный класс POSIX для пропускных символов (пробел, табуляция, новая строка) в текущем наборе NLS. Точка и дефис являются метасимволами, поэтому в шаблоне их необходимо экранировать обратной косой чертой. Наконец, ? означает ноль или одно вхождение предшествующего символа. Эта часть шаблона описывает необязательный пропуск, точку или дефис.
\d Эта часть шаблона описывает три цифры (см. выше).
[[:space:]\.\-]? Эта часть шаблона описывает необязательный пропуск, точку или дефис (см. выше).
\d Четыре цифры (см. выше).

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

Общий синтаксис REGEXP_SUBSTR :

Функция REGEXP_SUBSTR возвращает часть исходной_строки, совпадающую с шаблоном или подвыражением. Если совпадение не обнаружено, функция возвращает NULL . Здесь исходная_строка — строка, в которой выполняется поиск; шаблон — регулярное выражение, совпадение которого ищется в исходной_строке ; начальная_позиция — позиция, с которой начинается поиск; номер — порядковый номер совпадения (1 = первое, 2 = второе и т. д.); модификаторы — один или несколько модификаторов, управляющих процессом поиска.

Начиная с Oracle11g, также можно задать параметр подвыражение (1 = первое, 2 = второе и т. д.), чтобы функция возвращала начальную позицию заданного подвыражения (части шаблона, заключенной в круглые скобки). Подвыражения удобны в тех случаях, когда требуется найти совпадение для всего шаблона, но получить совпадение только для его части. Скажем, если мы хотим найти телефонный номер и извлечь из него код города, мы заключаем часть шаблона, описывающую код города, в круглые скобки, превращая ее в подвыражение:

Подсчет совпадений

Еще одна типичная задача — подсчет количества совпадений регулярного выражения в строке. До выхода Oracle11g программисту приходилось в цикле перебирать и подсчитывать совпадения. Теперь для этого можно воспользоваться новой функцией REGEXP_COUNT . Общий синтаксис ее вызова:

Замена текста REGEXP_REPLACE

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

Результат выглядит так:

При вызове функции REGEXP_REPLACE передаются три аргумента:

names — исходная строка;
'([a-z A-Z]*),([a-z A-Z]*),' — выражение, описывающее заменяемый текст (см. ниже);
'\1,\2 ' || chr(10) — текст замены. \1 и \2 — обратные ссылки, заложенные в основу нашего решения. Подробные объяснения также приводятся ниже.

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

([a-z A-Z]*) Совпадение должно начинаться с имени.
, За именем должна следовать запятая.
([a-z A-Z]*) Затем идет другое имя.
, И снова одна запятая.

Наша цель — заменить каждую вторую запятую символом новой строки. Вот почему выражение написано так, чтобы оно совпадало с двумя именами и двумя запятыми. Также запятые не напрасно выведены за пределы подвыражений.

Первое совпадение для нашего выражения, которое будет найдено при вызове REGEXP_REPLACE , выглядит так:

Два подвыражения соответствуют именам « Anna » и « Matt ». В основе нашего решения лежит возможность ссылаться на текст, совпавший с заданным подвыражением, через обратную ссылку. Обратные ссылки \1 и \2 в тексте замены ссылаются на текст, совпавший с первым и вторым подвыражением. Вот что происходит:

Вероятно, вы уже видите, какие мощные инструменты оказались в вашем распоряжении. Запятые из исходного текста попросту не используются. Мы берем текст, совпавший с двумя подвыражениями (имена «Anna» и «Matt»), и вставляем их в новую строку с одной запятой и одним символом новой строки.

Но и это еще не все! Текст замены легко изменить так, чтобы вместо запятой в нем использовался символ табуляции (ASCII-код 9):

Теперь результаты выводятся в два аккуратных столбца:

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

Максимализм и минимализм

Концепции максимализма и минимализма играют важную роль при написании регулярных выражений. Допустим, из разделенного запятыми списка имен нужно извлечь только первое имя и следующую за ним запятую. Список, уже приводившийся ранее, выглядит так:

Казалось бы, нужно искать серию символов, завершающуюся запятой:

Давайте посмотрим, что из этого получится:

Результат выглядит так:

Совсем не то. Что произошло? Дело в «жадности» регулярных выражений: для каждого элемента регулярного выражения подыскивается максимальное совпадение, состоящее из как можно большего количества символов. Когда мы с вами видим конструкцию:

у нас появляется естественное желание остановиться у первой запятой и вернуть строку « Anna ,». Однако база данных пытается найти самую длинную серию символов, завершающуюся запятой; база данных останавливается не на первой запятой, а на последней.

В версии Oracle Database 10g Release 1, в которой впервые была представлена поддержка регулярных выражений, возможности решения проблем максимализма были весьма ограничены. Иногда проблему удавалось решить изменением формулировки регулярного выражения — например, для выделения первого имени с завершающей запятой можно использовать выражение [^,]*, . Однако в других ситуациях приходилось менять весь подход к решению, часто вплоть до применения совершенно других функций.

Начиная с Oracle Database 10g Release 2, проблема максимализма отчасти упростилась с введением минимальных квантификаторов (по образцу тех, которые поддерживаются в Perl ). Добавляя вопросительный знак к квантификатору после точки, то есть превращая * в *?, я ищу самую короткую последовательность символов перед запятой:

Теперь результат выглядит так, как и ожидалось:

Минимальные квантификаторы останавливаются на первом подходящем совпадении, не пытаясь захватить как можно больше символов.

Подробнее о регулярных выражениях

Регулярные выражения на первый взгляд просты, но эта область на удивление глубока и нетривиальна. Они достаточно просты, чтобы вы начали пользоваться ими после прочтения этой статьи (хочется надеяться!), и все же вам предстоит очень много узнать. Некоторые источники информации от компании Oracle и издательства O’Reilly :

Читайте также: