Oracle DBA Forum  

Вернуться   Oracle DBA Forum > Oracle University Official Study Notes (ENG) > Oracle 10g SQL Fundamentals > Oracle 10g SQL Fundamentals II

Ответ
 
Опции темы Опции просмотра
  #1  
Старый 26.12.2009, 06:37
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,385
По умолчанию 08 Regular Expression Support

__________________
Чат форума (требуется аккаунт на github или twitter)
Ответить с цитированием
  #2  
Старый 26.12.2009, 06:37
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,385
По умолчанию Objectives



Objectives

In this lesson you learn to use the regular expression support feature that has been introduced in Oracle Database 10g.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 26.12.2009 в 06:51.
Ответить с цитированием
  #3  
Старый 26.12.2009, 06:37
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,385
По умолчанию Regular Expression Overview



Regular Expression Overview

Oracle Database 10g introduces support for Regular Expressions. The implementation complies with the Portable Operating System for UNIX (POSIX) standard, controlled by the Institute of Electrical and Electronics Engineers (IEEE), for ASCII data matching semantics and syntax. Oracles multilingual capabilities extend the matching capabilities of the operators beyond the POSIX standard. Regular expressions are a method of describing both simple and complex patterns for searching and manipulating.

String manipulation and searching contribute to a large percentage of the logic within a Web-based application. Usage ranges from the simple: find the word San Francisco in a specified text; to the complex extract of all URLs from the text; to the more complex: find all words whose every second character is a vowel.

When coupled with native SQL, the use of regular expressions allows for very powerful search and manipulation operations on any data stored in an Oracle database. You can use this feature to easily solve problems that would otherwise be very complex to program.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 26.12.2009 в 06:52.
Ответить с цитированием
  #4  
Старый 26.12.2009, 06:37
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,385
По умолчанию Meta Characters



Meta Characters

Meta characters are special characters that have a special meaning, such as a wildcard character, a repeating character, a nonmatching character, or a range of characters. You can use several predefined meta character symbols in the pattern matching.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 26.12.2009 в 06:52.
Ответить с цитированием
  #5  
Старый 26.12.2009, 06:37
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,385
По умолчанию Using Meta Characters



Using Meta Characters

1. In the first example, a simple match is performed.
2. In the second example, the any character is defined as a '.'. This example searches for the character a followed by any character, followed by the character c.
3. The third example searches for one or more occurrences of the letter a. The + character is used here to indicate a match of one or more of the previous characters.

You can search for nonmatching character lists too. A nonmatching character list allows you to define a set of characters for which a match is invalid. For example, to find anything but the characters a, b, or c, you can define the ^ to indicate a nonmatch.

Expression: [^abc]
Matches: abcdef
Matches: ghi
Does not match: abc

To match any letter not between a and i, you can use:

Expression: [^a-i]
Matches: hijk
Matches: lmn
Does not match: abcdefghi
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 26.12.2009 в 06:52.
Ответить с цитированием
  #6  
Старый 26.12.2009, 06:37
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,385
По умолчанию

__________________
Чат форума (требуется аккаунт на github или twitter)
Ответить с цитированием
  #7  
Старый 26.12.2009, 06:37
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,385
По умолчанию Regular Expression Functions



Regular Expression Functions

The Oracle Database 10g provides a set of SQL functions that you can use to search and manipulate strings using regular expressions. You can use these functions on any data type that holds character data such as CHAR, NCHAR, CLOB, NCLOB, NVARCHAR2, and VARCHAR2. A regular expression must be enclosed or wrapped between single quotation marks. Doing so ensures that the entire expression is interpreted by the SQL function and can improve the readability of your code.

REGEXP_LIKE: This function searches a character column for a pattern. Use this function in the WHERE clause of a query to return rows matching the regular expression you specify.
REGEXP_REPLACE: This function searches for a pattern in a character column and replaces each occurrence of that pattern with the pattern you specify.
REGEXP_INSTR: This function searches a string for a given occurrence of a regular expression pattern. You specify which occurrence you want to find and the start position to search from. This function returns an integer indicating the position in the string where the match is found.
REGEXP_SUBSTR: This function returns the actual substring matching the regular expression pattern you specify.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 26.12.2009 в 06:53.
Ответить с цитированием
  #8  
Старый 26.12.2009, 06:38
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,385
По умолчанию The REGEXP Function Syntax



The REGEXP Function Syntax

The following table contains descriptions of the terms shown in the syntax on the slide.

__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 26.12.2009 в 06:53.
Ответить с цитированием
  #9  
Старый 26.12.2009, 06:38
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,385
По умолчанию Performing Basic Searches



Example of REGEXP_LIKE

In this query, against the EMPLOYEES*table, all employees with first names containing either Steven or Stephen are displayed. In the expression used,
'^Ste(v|ph)en$' :

^ indicates the beginning of the sentence
$ indicates the end of the sentence
| indicates either/or
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 26.12.2009 в 06:55.
Ответить с цитированием
  #10  
Старый 26.12.2009, 06:38
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,385
По умолчанию Checking the Presence of a Pattern



Checking the Presence of a Pattern

In this example, the REGEXP_INSTR function is used to search the street address to find the location of the first nonalphabetic character, regardless of whether it is in upper or lower case. The search is performed only on those addresses that do not start with a number. Note that [:<class>:] implies a character class and matches any character from within that class; [:alpha:] matches with any alphabetic character. The results are displayed.

In the expression used in the query '[^[:alpha:]]':

[ starts the expression
^ indicates NOT
[:alpha:] indicates alpha character class
] ends the expression


Note: The POSIX character class operator enables you to search for an expression within a character list that is a member of a specific POSIX character class. You can use this operator to search for specific formatting, such as uppercase characters, or you can search for special characters such as digits or punctuation characters. The full set of POSIX character classes is supported. Use the syntax [:class:] where class is the name of the POSIX character class to search for. The following regular expression searches for one or more consecutive uppercase characters : [[:upper:]]+ .
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 26.12.2009 в 06:55.
Ответить с цитированием
Ответ
Опции темы
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.

Быстрый переход


Текущее время: 23:56. Часовой пояс GMT +3.


Powered by vBulletin®