Oracle’s regular expression in sql and pl/sql

Разделители¶

Разделители строк

RegEx	Находит
	начало строки
	конец строки
	начало текста
	конец текста
	любой символ в строке
	только если он находится в начале строки
	, только если он в конце строки
	только если это единственная строка в строке
	, , и так далее

Метасимвол по умолчанию соответствует началу входной строки. — конец.

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

Обратите внимание, что в последовательности нет пустой строки. Примечание

Примечание

Если вы используете , то / также соответствует , , , или .

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

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

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

Примечание

Многострочная обработка может быть настроена с помощью свойств и .

Таким образом, вы можете использовать разделители стиля Unix или стиль DOS / Windows или смешивать их вместе (как описано выше по умолчанию).

Если вы предпочитаете математически правильное описание, вы можете найти его на сайте www.unicode.org.

Синтаксис

Регулярные выражения в python

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

REGEXP_INSTR( string, pattern ] ] ] ] )

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

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 раз.

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

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

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

subexpression Необязательный. Используется, когда шаблон имеет подвыражения, и вы хотите указать, какое подвыражение в шаблоне является целью. Это целочисленное значение от 0 до 9, указывающее, что подвыражение соответствует шаблону.

Функция REGEXP_INSTR возвращает числовое значение. Если функция REGEXP_INSTR не обнаруживает никакого соответствия шаблону, она вернет 0.

Example 8: Case sensitive search using T-SQL RegEx functions

Mysql substr() function

By default, we do not get case sensitive results. For example, the following queries return the same result set:

1
2
3
4
5
6
7

SELECTDescription

FROMAdventureWorks.Production.ProductDescription

whereDescriptionlike’%%’

SELECTDescription

FROMAdventureWorks.Production.ProductDescription

whereDescriptionlike’%%’

We can perform case sensitive search using the following two ways:

Database collation setting: Each database in SQL Server have a collation. Right-click on the database and in the properties page, you can see the collation

We have SQL_Latin1_General_CP1_CI_AS performs case insensitive behaviour for the database. We can change this collation to case sensitive collation. It is not a simple solution. It might create issues for your queries. It is not a recommended way unless you explicitly require case sensitive collation.

We can use Column Collation with T-SQL RegEx functions to perform case sensitive search.

1
2
3
4
5
6
7

CreatetableCharacters

(Alphabetchar(1)

)

Go

InsertintoCharactersvalues(‘A’)

InsertintoCharactersvalues(‘a’)

Go

In the table, we have letter A in upper and lowercase. If we run the following select statement, it returns both uppercase and lowercase:

1
2

SELECT*fromCharacters

whereAlphabetlike’%’

Suppose we want to filter the uppercase letter in the result. We can use column collation as per the following query:

1
2

select*fromCharacters

whereAlphabetCOLLATELatin1_General_BINlike’%’

It returns uppercase letter A in the output.

Similarly, the following query returns lowercase letter in the output:

1
2

select*fromCharacters

whereAlphabetCOLLATELatin1_General_BINlike’%’
We can use T-SQL RegEx function to find both upper and lowercase characters in the output.

We want the following output:
- The first character should be uppercase character C
- The second character should be lowercase character h
- Rest of the characters can be in any letter case
1
2
3

SELECTDescription

FROMAdventureWorks.Production.ProductDescription

whereDescriptionCOLLATELatin1_General_BINlike’%’

Example — Match on more than one alternative

Vba instr

The next example that we will look at involves using the pattern. The pattern is used like an «OR» to specify more than one alternative.

For example:

SELECT REGEXP_REPLACE ('Anderson', 'a|e|i|o|u', 'G')
FROM dual;

Result: 'AndGrsGn'

This example will return ‘AndGrsGn’ because it is searching for the first vowel (a, e, i, o, or u) in the string. Since we did not specify a match_parameter value, the REGEXP_REPLACE function will perform a case-sensitive search which means that the ‘A’ in ‘Anderson’ will not be matched.

We could modify our query as follows to perform a case-insensitive search as follows:

SELECT REGEXP_REPLACE ('Anderson', 'a|e|i|o|u', 'G', 1, 0, 'i')
FROM dual;

Result: 'GndGrsGn'

Now because we have provide a match_parameter of ‘i’, the query will replace ‘A’ in the string. This time, the ‘A’ in ‘Anderson’ will be found as a match. Notice also that we specified 0 as the 5th parameter so that all occurrences would be replaced.

Now, let’s quickly show how you would use this function with a column.

So let’s say we have a contact table with the following data:

contact_id	last_name
1000	Anderson
2000	Smith
3000	Johnson

Now, let’s run the following query:

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

These are the results that would be returned by the query:

Oracle REGEXP_LIKE Function

The REGEXP_LIKE function searches a column for a specified pattern. It’s used in a WHERE clause to check if a column matches a pattern, and if it does, then the row is included in the result set.

It’s similar to the LIKE condition, but allows for regular expressions.

The syntax for the REGEXP_LIKE function is:

The parameters are:

source_string (mandatory): The value that is searched in. It can be any data type of CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
pattern (mandatory): This is the regular expression that you provide. It can be up to 512 bytes.
match_parameter (optional): This allows you to change the default matching behaviour of the function, which can be one or more of:
- “i”: case-insensitive matching
- “c”: case-sensitive matching
- “n”: allows the “.” character to match the newline character instead of any character
- “m”: treats the source_string value as multiple lines, where ^ is the start of a line and $ is the end of a line.

If you specify multiple match_parameter values that contradict each other (e.g. “ci” which matches to case-sensitive and case-insensitive), then Oracle uses the last value. In this example, it will use “i” instead of “c”.

If you don’t specify a match parameter, then:

The default case sensitivity is determined by the parameter NLS_SORT.
The period character doesn’t match the newline character
The source string is treated as a single line and not multiple lines.

Oracle REGEXP_REPLACE Examples

Let’s see some examples of the REGEXP_REPLACE function.

Example 1

This example removes all occurrences of two consecutive vowels.

TITLE	REG
Box	Box
Chair	Chr
Vacuum	Vacm
Desk	Desk
Round	Rnd
Under	Under
Waterfall	Waterfall
Dismiss	Dismiss
Superficial	Superficl
Suspicious	Suspics

As you can see, the “ai” in “Chair” was removed, but “Box” remained the same, because “Box” only has one consecutive vowel.

Example 2

This example replaces two consecutive vowels with two dashes.

TITLE	REG
Box	Box
Chair	Ch–r
Vacuum	Vac–m
Desk	Desk
Round	R–nd
Under	Under
Waterfall	Waterfall
Dismiss	Dismiss
Superficial	Superfic–l
Suspicious	Suspic–s

Example 3

This example replaces two consecutive vowels of the same vowel with two dashes.

TITLE	REG
Box	Box
Chair	Chair
Vacuum	Vac–m
Desk	Desk
Round	Round
Under	Under
Waterfall	Waterfall
Dismiss	Dismiss
Superficial	Superficial
Suspicious	Suspicious

As you can see, “Vacuum” had the “uu” replaced, but “Chair” did not, as the vowels in “Chair” are not the same.

Example 4

This example replaces any digits in the string with a + and then the digit.

TITLE	REG
Yeti	Yeti
Zucchini	Zucchini
Summer of 69	Summer of +6+9
The year is 2017	The year is +2+0+1+7
1955	+1+9+5+5

Example 5

This example replaces any vowel character followed by any letter from “a” to “m”, starting from position 4, with two dashes.

TITLE	REG
Box	Box
Chair	Chair
Vacuum	Vacu–
Desk	Desk
Round	Round
Under	Under
Waterfall	Waterf–l
Dismiss	Dismiss
Superficial	Superf—-l
Suspicious	Susp–ious

Example 6

This example replaces the second occurrence of any vowel character followed by any letter from “a” to “m”, starting from position 1, with two dashes.

TITLE	REG
Box	Box
Chair	Chair
Vacuum	Vacu–
Desk	Desk
Round	Round
Under	Under
Waterfall	Waterfall
Dismiss	Dismiss
Superficial	Superfic–l
Suspicious	Suspicious

Example 7

This example replaces more than one consecutive capitalised letter with an underscore, starting from position 2.

TITLE	REG
Mathematical	Mathematical
Press	Press
QUICKLY	Q_
Roger	Roger
Simple	Simple

Синтаксис

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

REGEXP_SUBSTR( string, pattern ] ] ] )

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

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 раз.

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

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

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

Функция REGEXP_SUBSTR возвращает строковое значение.
Если функция REGEXP_SUBSTR не обнаруживает какого-либо вхождения шаблона, она возвращает NULL.

Example — Match on Multiple Characters

Let’s look next at how we would use the REGEXP_INSTR function to match on a multi-character pattern.

For example:

SELECT REGEXP_INSTR ('The example shows how to use the REGEXP_INSTR function', 'ow', 1, 1, 0, 'i')
FROM dual;

Result: 15

This example will return the first occurrence of ‘ow’ in the string. It will match on the ‘ow’ in the word ‘shows’.

We could change the starting position of the search so that we perform the search starting from the middle of the string.

For example:

SELECT REGEXP_INSTR ('The example shows how to use the REGEXP_INSTR function', 'ow', 16, 1, 0, 'i')
FROM dual;

Result: 20

This example will start the search for the pattern of ‘ow’ at position 16 in the string. In this case, it will skip over the first 15 characters in the string before searching for the pattern.

Now, let’s look how we would use the REGEXP_INSTR function with a table column and search for multiple characters.

For example:

SELECT REGEXP_INSTR (other_comments, 'the', 1, 1, 0, 'i')
FROM contacts;

Introduction

We store data in multiple formats or data types in SQL Server tables. Suppose you have a data column that contains string data in alphanumeric format. We use LIKE logical operator to search specific character in the string and retrieve the result. For example, in the Employee table, we want to filter results and get the only employee whose name starts with character A.

We use regular expressions to define specific patterns in T-SQL in a LIKE operator and filter results based on specific conditions. We also call these regular expressions as T-SQL RegEx functions. In this article, we will use the term T-SQL RegEx functions for regular expressions.

We can have multiple types of regular expressions:

Alphabetic RegEx
Numeric RegEx
Case Sensitivity RegEx
Special Characters RegEx
RegEx to Exclude Characters

Arguments

The Oracle function accepts 6 arguments:

is a string to be searched for.

is the regular expression pattern that is used to search for in the source string.

is positive integer that indicates the starting position in the source string where the search begins.

The argument is optional. Its default value is 1. Therefore, if you don’t specify it explicitly, the function will start searching at the beginning of the source string.

is a positive integer that specifies which occurrence of the search pattern that the function should search for.

The argument is also optional and it defaults to 1, meaning that the function should search for the first occurrence of the pattern in the source string.

is a literal string that determines the default matching behavior for the function.

You can use one or more following values for the argument:

‘i’ indicates case-insensitive matching
‘c’ indicates case-sensitive matching.
‘n’ allows the period (.) character to match the newline character. If you don’t explicitly specify this parameter, the function will not use the period to match the newline character.
‘m’ treats the source string as a multiline string.

Because the argument is optional, therefore, if you omit it, the function will behave as follows:

Case sensitivity matching is determined by parameter.
The period (.) does not match the newline character.
The source string is treated as a single line.

is a positive integer whose value from 0 to 9 that indicates which sub-expression in the regular expression is the target.

Oracle REGEXP_INSTR Examples

Let’s take a look at some examples of the Oracle REGEXP_INSTR function.

This example finds the position of the “ee” within a string.

TITLE	REG
Tree	3
Freedom	3

Example 2

This example finds the position of a string that starts with either A, B, or C, and then has 4 alphabetical characters following it.

TITLE	REG
Chair	1
Crypt	1

Example 3

This example finds the position of strings that have two vowels in a row.

TITLE	REG
Chair	3
Vacuum	4
Round	2
Superficial	9
Suspicious	7
Tree	3
breakfast	3
Freedom	3
Helium	4
Laundromat	2
Exclaim	5
Vain	2
The year is 2017	6

As you can see, the REGEXP_INSTR value is different for each row depending on where the two vowels start.

Example 4

This example shows the position of values where there are two vowels in a row, after position 4.

TITLE	REG
Vacuum	4
Superficial	9
Suspicious	7
Helium	4
Exclaim	5
The year is 2017	6

Example 5

This example shows the position of the second occurrence in a string where there is a vowel after position 5.

TITLE	REG
Superficial	9
Suspicious	7
Designate	9
Hawkeye	7
Laundromat	9
Mathematical	7
Exclaim	6
Undercover	9
Xylophone	9
Zucchini	8
Summer of 69	8
The year is 2017	7

Example 6

This example shows the position of the second occurrence in a string where there is a vowel after position 5, but shows the position at the end of the value that was found.

TITLE	REG
Superficial	10
Suspicious	8
Designate	10
Hawkeye	8
Laundromat	10
Mathematical	8
Exclaim	7
Undercover	10
Xylophone	10
Zucchini	9
Summer of 69	9
The year is 2017	8

Example 7

This example shows the position of values that have an A, B, C, D, or E, followed by a vowel, using a case-insensitive search.

TITLE	REG
Box	1
Chair	3
Vacuum	3
Desk	1
Under	3
Dismiss	1
Superficial	8
Suspicious	6
Tree	3
breakfast	3
Designate	1

Only some of the values are shown here. “breakfast’ is shown because the search is case-insensitive, so it doesn’t matter that it has lowercase values.

Example 8

This example shows values that have an A, B, or C, followed by a vowel, and finds the position of the vowel.

TITLE	REG
Box	2
Chair	4
Vacuum	4
Superficial	9
Suspicious	7
November	7
October	6
Laundromat	3
Mathematical	11
Exclaim	6
Undercover	7
Vain	3

Oracle PL/SQL программирование

Data Types	Типы данных
Literals	Такие как text, integer, number и datetime.
Declaring Variables	Объявление переменных, констант
Comments within SQL	Комментарии SQL используются для комментирования кода программ
Procedures	Создание и удаление процедур
Functions	Создание и удаление функций
Triggers	Создание, удаление, включение и отключение триггеров
Dynamic SQL	Динамический SQL
Packages	Пакеты и тела пакетов — создание, удаление
Sequences	Последовательности
Synonyms	Синонимы
Aliases	Псевдонимы (алиасы) для таблиц или столбцов
WITH	Оператор WITH


Объявление курсора (Declare a Cursor)
Операторы
OPEN	FETCH	CLOSE
Атрибуты курсора (Cursor Attributes: %FOUND, %NOTFOUND, %ISOPEN, ROWCOUNT)
SELECT FOR UPDATE оператор
WHERE CURRENT OF оператор

Шаг 15 — Оператор LIKE

Сейчас пожалуй настало время самого, на мой взгляд, употребляемого выражения в операциях поиска данных в таблицах — LIKE! На первый взгляд ничего особо примечательного в нем нет! Но это только на первый взгляд … 🙂 А вот если присмотреться по внимательнее, то можно заметить кое что интересное. Итак, синтаксис по стандарту SQL92:

--- ИМЯ СТОЛБЦА --------- LIKE (шаблон) -----------------------------------
		   NOT			   ESCAPE (имя пропуска)

Вот так казалось бы все просто, однако это не так! Давайте выполним простой запрос: Показать лимит кредита для компании «Апельсин»:

SQL> 1  SELECT COMPANY, CREDIT_LIMIT
     2  FROM CUSTOMERS
     3  WHERE COMPANY  = 'Апельсин'
     4  /

COMPANY                        CREDIT_LIMIT
------------------------------ ------------
Апельсин                             50,834

Чего проще! А вот если оператор при вводе спутал третью букву в слове, «Апельсин» тогда при запуске запроса вы ничего не получите! Либо вы просто забыли как там называлась эта компания не то «Злыдень корпорейтед», не то «Студень лимитид», да это собственно и не важно, скажем вы просто помните, что там звучало слово скажем «Клоун»! Теперь следует применить оператор LIKE с шаблоном ‘%’ и все встанет на свои места! Например вот так:

SQL> SELECT COMPANY, CREDIT_LIMIT
  2  FROM CUSTOMERS
  3  WHERE COMPANY LIKE '%н'
  4  /

COMPANY                        CREDIT_LIMIT
------------------------------ ------------
Смешной клоун                        35,645
Апельсин                             50,834

В данном случае говориться, а именно — LIKE ‘%н’ покажи мне все записи, которые оканчиваются на букву ‘н‘, то есть, если шаблон ‘%‘ стоит первый, а его заканчивает некое скажем — ‘бесы‘, то при выполнении запроса увидим:

SQL> SELECT COMPANY, CREDIT_LIMIT
  2  FROM CUSTOMERS
  3  WHERE COMPANY LIKE '%бесы'
  4  /

COMPANY                        CREDIT_LIMIT
------------------------------ ------------
Безбашенные балбесы                  20,765
Просто Балбесы			     60,653

А это именно и означает что, начальные буквы выражения нам «по барабану», но оканчиваться оно должно так как мы и просим! Что собственно и требовалось доказать! Если написать ‘С%’, догадались? Естественно получим все записи начинающиеся на букву ‘С‘, а если написать, ‘Су%‘, то можно найти все, что касается «КБ Сухого», но это если проломитесь в БД самого КБ! Дерзайте!!! Ни кто не запретит и такое выражение ‘%шар%’, которое гласит начало и окончание нам «по барабану», а вот словосочетание ‘шар’, нас особенно интересует!

Сразу хочу предупредить, что если ввести, что-то типа WHERE COMPANY LIKE ‘%’, можно нарваться на неприятность, а именно: WHERE COMPANY LIKE ‘%’ эквивалентно, если бы вы WHERE оператор вообще не писали, а так и нужно было бы поступить, если бы вы были в здравом уме и действительно хотели получить все содержимое таблицы! Да именно, такое выражение вернет вам все записи в таблице! Хорошо если она не большая, а если в ней миллион, другой строк, то придется пристрелить SQLPlus, все равно не дождетесь результата, да и зачем это нужно!

Еще иногда вместо шаблона ‘%’ применяется знак ‘*’, например в MS SQL, ну и кто может быть знаком с DOS и не раз применял что-то вроде c:\>dir *.exe! :)))

А что если действительно спутан только один символ? Как я говорил в самом начале, тогда применяем шаблон ‘_’ — это означает, что вы не уверены, например в правильности ввода какого либо слова! Например:


SQL> SELECT COMPANY, CREDIT_LIMIT
  2  FROM CUSTOMERS
  3  WHERE COMPANY LIKE 'Ап_льсин'
  4  /

COMPANY                        CREDIT_LIMIT
------------------------------ ------------
Апельсин                             50,834

Какой бы не была пропущенная буква вы обязательно что-нибудь найдете!!! Пробуйте!!!

А вот еще одна ситуация, которая может встретиться, не так часто, но все же! Что, если имя компании начинается на ‘%%%’. Что тогда? А все очень просто — применяем «убегающие символы»! А именно последовательности пропуска, которые состоят из выражения ESCAPE и собственно строки или символа пропуска, в качестве которого вы можете применить например ‘$’, тогда символ после ‘$’ будет считаться литералом, а далее можно снова использовать подстановочный символ вот так:

WHERE PRODUCT LIKE 'A$%BS%' ESCAPE '$'

Еще раз, символ идущий за ‘$’, считается литералом, а второй символ ‘%’, считается подстановочным то есть «по барабану»! Понятно! Но вот в умных, книгах рекомендуется таких выражений и сложностей избегать! А, а вообще это все мне например напоминает, работу с регулярными выражениями, которые гораааааздооо сложнее! Но, интересно! Вот такие приключения! :)) На последок, поэкспериментируйте с оператором LIKE и сами сможете убедиться, на сколько он гибок и удобен!

Предыдущий Шаг | Следующий Шаг | ОглавлениеАвтор .

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

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

REGEXP_INSTR (исходная_строка, шаблон ]]]])

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

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

DECLARE
names VARCHAR2(60) := 'Anna,Matt,Joe,Nathan,Andrew,Jeff,Aaron';
names_adjusted VARCHAR2(61);
comma_delimited BOOLEAN;
j_location NUMBER;
BEGIN
-- Поиск по шаблону
comma_delimited := REGEXP_LIKE(names,'^(*,)+(*)$', 'i');
-- Продолжить, только если действительно был обнаружен список,
-- разделенный запятыми.
IF comma_delimited THEN
j_location := REGEXP_INSTR(names, 'A*,|A*$');
DBMS_OUTPUT.PUT_LINE( J_location);
END IF;
END;

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

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

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

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

Complete Regular Expressions Tutorial

Do not worry if the above example or the quick start make little sense to you. Any non-trivial regex looks daunting to anybody not familiar with them. But with just a bit of experience, you will soon be able to craft your own regular expressions like you have never done anything else. The free Regular-Expressions.info Tutorial explains everything bit by bit.

This tutorial is quite unique because it not only explains the regex syntax, but also describes in detail how the regex engine actually goes about its work. You will learn quite a lot, even if you have already been using regular expressions for some time. This will help you to understand quickly why a particular regex does not do what you initially expected, saving you lots of guesswork and head scratching when writing more complex regexes.

Example — Match on nth_occurrence

The next example that we will look at involves the nth_occurrence parameter. The nth_occurrence parameter allows you to select which occurrence of the pattern you wish to return the position of.

First Occurrence

Let’s look at how to find the first occurrence of a pattern in a string.

For example:

SELECT REGEXP_INSTR ('TechOnTheNet', 'a|e|i|o|u', 1, 1, 0, 'i')
FROM dual;

Result: 2

This example will return 2 because it is searching for the first occurrence of a vowel (a, e, i, o, or u) in the string.

Second Occurrence

Next, we will search for the second occurrence of a pattern in a string.

For example:

SELECT REGEXP_INSTR ('TechOnTheNet', 'a|e|i|o|u', 1, 2, 0, 'i')
FROM dual;

Result: 5

This example will return 5 because it is searching for the second occurrence of a vowel (a, e, i, o, or u) in the string.

Examples

Find groups of «word characters» (letters, numbers and underscore) ending with «thy» in the string «healthy, wealthy, and wise» and replace them with nothing.

=> SELECT REGEXP_REPLACE('healthy, wealthy, and wise','\w+thy');
 REGEXP_REPLACE 
----------------
 , , and wise
(1 row)

Find groups of word characters ending with «thy» and replace with the string «something.»

=> SELECT REGEXP_REPLACE('healthy, wealthy, and wise','\w+thy', 'something');
         REGEXP_REPLACE         
--------------------------------
 something, something, and wise
(1 row)

Find groups of word characters ending with «thy» and replace with the string «something» starting at the third character in the string.

=> SELECT REGEXP_REPLACE('healthy, wealthy, and wise','\w+thy', 'something', 3);
          REGEXP_REPLACE          
----------------------------------
 hesomething, something, and wise
(1 row)

Replace the second group of word characters ending with «thy» with the string «something.»

=> SELECT REGEXP_REPLACE('healthy, wealthy, and wise','\w+thy', 'something', 1, 2);
        REGEXP_REPLACE        
------------------------------
 healthy, something, and wise
(1 row)

Find groups of word characters ending with «thy» capturing the letters before the «thy», and replace with the captured letters plus the letters «ish.»

=> SELECT REGEXP_REPLACE('healthy, wealthy, and wise','(\w+)thy', '\1ish');
       REGEXP_REPLACE       
----------------------------
 healish, wealish, and wise
(1 row)

Create a table to demonstrate replacing strings in a query.

=> CREATE TABLE customers (name varchar(50), phone varchar(11));
CREATE TABLE
=> CREATE PROJECTION customers1 AS SELECT * FROM customers;
CREATE PROJECTION
=> COPY customers FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> Able, Adam|17815551234
>> Baker,Bob|18005551111
>> Chu,Cindy|16175559876
>> Dodd,Dinara|15083452121
>> \.

Query the customers, using REGEXP_REPLACE to format the phone numbers.

=> SELECT name, REGEXP_REPLACE(phone, '(\d)(\d{3})(\d{3})(\d{4})', 
'\1-(\2) \3-\4') as phone FROM customers;
    name     |      phone       
-------------+------------------
 Able, Adam  | 1-(781) 555-1234
 Baker,Bob   | 1-(800) 555-1111
 Chu,Cindy   | 1-(617) 555-9876
 Dodd,Dinara | 1-(508) 345-2121
(4 rows)