Shulou(Shulou.com)05/31 Report--

Oracle in how to use regular functions, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Use these functions to search for character patterns in strings. For example, suppose you have the following years:

1965196819711970

If you want years between 1965 and 1968 (including 1965 and 1968), you can do this using the following regular expression:

^ 196 [5-8] $

Regular expressions contain many metacharacters (metacharacter). In the above example, ^, [5-8], and $are metacharacters. ^ can match the beginning of a string; [5-8] can match a number between 5 and 8; and $can match the end of a string. Therefore, ^ 196 can match a string that begins with 196; [5-8] $can match a string that ends in 5, 6, 7, or 8. And ^ 196 [5-8] $can match 1965, 1966, 1967 and 1968, which is the desired result.

In the following example, suppose you have the following string that quotes a line from Shakespeare's Romeo and Juliet:

But, soft! What light through yonder window breaks?

If you want to find the substring light, you can apply the following regular expression to the referenced string:

L [[: alpha:]] {4}

In this example, [[: alpha:]] and {4} are metacharacters. [[: alpha:]] can match characters between Amurz or Amurz; {4} means that the previous matching pattern can be repeated four times. When l, [: alpha:]] and {4} are used together, you can match a sequence of five letters that begin with l. Therefore, when you apply the regular expression l [[: alpha:]] {4} to this string, you can match the substring light.

Table 4-7 lists some of the metacharacters that can be used in regular expressions, along with the meaning of these metacharacters and simple examples of using them.

Table 4-7 Metacharacters in regular expressions

Meta character

Ideation

Example son

\

Indicates that the character to be matched is a special character, constant, or reverse reference. (the reverse reference repeats the previous match.)

\ nmatch newline characters

\ match\

\ (match (

\) match)

^

Matches the beginning of a string

If An is the first character in the string, ^ A matches A.

$

Match the end position of the string

If B is the last character in the string, $B matches B.

*

Matches the previous character 0 or more times.

Ba*rk can match brk, bark, baark, and so on.

+

Matches the previous character one or more times.

Ba+rk can match bark, baark, etc., but not brk

?

Match the previous character 0 or 1 times

Ba?rk can only match brk and bark

{n}

Matches a character exactly n times, where n is an integer

Hob {2} it can match hobbit

(continuation of table)

Meta character

Ideation

Example son

{n,m}

Match a character at least n times and m times at most, where n and m are integers

Hob {2Pol 3} it can only match hobbit and hobbbit

.

Match any single character except null

Hob.it can match hobait, hobbit, etc.

(pattern)

Matches a subexpression of the specified pattern. You can use subexpressions to form complex regular expressions. In this seed expression, a single match, called capture, can be accessed.

Anatom (y | ies) can match anatomy and anatomies

X | y

Match x or y, where x and y are one or more characters

War | peace can match war or peace

[abc]

Match any character in parentheses

[ab] bc can match abc and bbc

[aMuz]

Matches any character in the specified range

[Amurc] bc can match abc, bbc and cbc

[::]

Specify a character class that can match any character in the class

[: alphanum:] can match characters 0-9, Amurz and Amurz

[: alpha:] can match characters Amurz and Amurz

[: blank:] can match spaces or tab key

[: digit:] can match the numbers 0-9

[: graph:] can match non-null characters

[: lower:] can match the lowercase letter Amurz

[: print:] is similar to [: graph:] except that [: print:] includes space characters

[: punct:] can match punctuation marks,'', etc.

[: space:] can match all white space characters

[: upper:] can match all uppercase letters Apocz

[: xdigit:] can match the hexadecimal digits 0,9, AUBF, and axif

[..]

Match a composite element, such as a multi-character element

None

[=]

Specify equivalence class

None

\ n

This is a reverse reference to the previous capture, where n is a positive integer

(.)\ 1 can match two consecutive identical characters. (.) You can match any single character except null, while\ 1 repeats what you matched last time, that is, you match the same character again, so you can match two consecutive identical characters

Oracle Database 10g Release 2 adds a lot of metacharacters similar to Perl, as shown in Table 4-8.

Table 4-8 metacharacters similar to Perl

Meta character

Implied meaning

\ d

Numeric character

\ d

Non-numeric character

\ w

Alphabetic character

\ W

Non-alphabetic character

\ s

White space character

\ s

Non-white space character

(continuation of table)

Meta character

Implied meaning

\ a

Matches only the beginning of the string

\ Z

Matches only the position at the end of the string or before the newline at the end of the string

*?

Match the previous pattern element 0 or more times

+?

Match the previous pattern element one or more times

??

Match the previous pattern element 0 or 1 times

{n}

Match the previous pattern element exactly n times

{n,}

Match the previous pattern element at least n times

{n,m}

Match the previous pattern element at least n times, but not more than m times

Table 4-9 lists the regular expression functions. The regular expression function is new in Oracle Database 10g, and some more entries have been added to Oracle Database 11g, as shown in the following table.

Table 4-9 regular expression functions

Function

State clearly

REGEXP_LIKE (x, pattern)

[, match_option])

Search for the regular expression defined in the pattern parameter from x. The default matching option can be modified using match_option, which can be set to:

'cpermission, indicating case sensitivity when matching (default option)

'Inotify, indicating that the match is case-insensitive

'ncharacters, allowing the use of operators that can match any character

'mforth, using x as a string containing multiple lines

REGEXP_INSTR (x, pattern)

[, start

[, occurrence

[, return_option

[, match_option

[, subexp_option])

Look for pattern in x and return the location of pattern. You can specify the following optional parameters:

The location where start started looking. The default value is 1, which means the first character of x.

Occurrence indicates that the location of the first occurrence of the pattern should be returned. The default value is 1, which means that the function returns the location where pattern first appeared in x.

Return_option indicates what integer should be returned. If the parameter is 0, the integer to be returned is the position of the first character in x; if the parameter is a non-zero integer, the integer to be returned is the position of the character in x that appears after pattern

Match_option modifies the default matching settings to work in the same way as specified in REGEXP_LIKEK ().

Subexp_option is a new addition to Oracle Database 11g and works as follows: for patterns with subexpressions, subexp_option is a non-negative number between 0and 9, indicating which subexpression in pattern is the target of the function. For example, consider the expression 0123 ((abc) (de) f) ghi) 45 (678), which has five subexpressions: "abcdefghi", "abcdef", "abc", "de", and "678".

If subexp_option is 0, the location of pattern is returned. If pattern does not have the correct subexpression number, the function returns 0. If subexp_option is null, null is returned. The default value for subexp_option is 0

(continuation of table)

Function

State clearly

REGEXP_REPLACE (x, pattern)

[, replace_string

[, start

[, occurrence

[, match_option])

Look for pattern in x and replace it with replace_string. The other options mean exactly the same as the parameters of the REGEXP_INSTR () function

REGEXP_SUBSTR (x, pattern)

[, start

[, occurrence

[, match_option

[, subexp_option])

Returns a substring in x that can match pattern, whose starting position is specified by start. The other options mean exactly the same as the arguments to the REGEXP_INSTR () function. The newly added subexp_option of Oracle Database 11g works in the same way as in the REGEXP_INSTR () function

REGEXP_COUNT (x, pattern)

[, start

[, match_option]])

This is a new function added to Oracle Database 11g. Look for pattern in x and return the number of times pattern appears in x. The following two optional parameters can be provided:

The location where start started looking. The default value is 1, which means the first character of x.

Match_option modifies the default matching settings to work the same way as in REGEXP_LIKEK ()

The next few sections will tell you more about regular expression functions.

1. REGEXP_LIKE ()

REGEXP_LIKE (x, pattern [, match_option]) is used to find the regular expression defined in the pattern parameter in x, and the function can also provide an optional parameter, match_option, which can be set to one of the following characters:

● 'clocations, indicating case sensitivity when matching (default option)

● 'ified, indicating that the match is case-insensitive

● 'ncharacters, allowing the use of operators that can match any character

● 'mstrings, using x as a string containing multiple lines

The following query uses the REGEXP_LIKE function to retrieve customers born between 1965 and 1968:

SELECT customer_id, first_name, last_name, dobFROM customersWHERE REGEXP_LIKE (TO_CHAR (dob, 'YYYY'),' ^ 196 [5-8] $')

CUSTOMER_ID FIRST_NAME LAST_NAME DOB

--

1 John Brown 01-JAN-65

2 Cynthia Green 05-FEB-68

The following query retrieves customers whose names start with J or j. Note that the regular expression passed to REGEXP_LIKE () is ^ j, and the match option is I, which means that it is not case-sensitive, so in this case, ^ j can match J or j:

SELECT customer_id, first_name, last_name, dobFROM customersWHERE REGEXP_LIKE (first_name,'^ jacks,'i')

CUSTOMER_ID FIRST_NAME LAST_NAME DOB

--

1 John Brown 01-JAN-65

2. REGEXP_INSTR ()

REGEXP_INSTR (x, pattern [, start [, occurrence [, return_option [, match_option]) is used to find the location where pattern;REGEXP_INSTR () returns the occurrence of pattern in x. The matching position starts at 1.

The following query uses the REGEXP_INSTR function to return the location that matches the regular expression l [[: alpha:]] {4}:

SELECTREGEXP_INSTR ('But, soft! What light through yonder window breaks?','l [[: alpha:]] {4}') AS resultFROM dual;RESULT-17

Notice that the return value is 17, which is the position of l in light.

The following query returns the position of the second matching regular expression s [[: alpha:]] {3}, starting with 1:

SELECTREGEXP_INSTR ('But, soft! What light through yonder window softly breaks?','s [[: alpha:]] {3}', 1, 2) AS resultFROM dual

RESULT

-

forty-five

The following query uses the REGEXP_INSTR function to return the position of the letter o for the second match, starting at 10:

SELECTREGEXP_INSTR ('But, soft! What light through yonder window breaks?','o', 10, 2) AS resultFROM dual;RESULT-32

3. REGEXP_REPLACE ()

REGEXP_REPLACE (x, pattern [, replace_string [, start [, occurrence [, match_option]) is used to find pattern in x and replace it with replace_string.

The following query uses the REGEXP_REPLACE function to replace the substring that matches the regular expression l [[: alpha:]] {4} with the string sound:

SELECTREGEXP_REPLACE ('But, soft! What light through yonder window breaks?','l [[: alpha:]] {4}', 'sound') AS resultFROM dual;RESULT---But, soft! What sound through yonder window breaks?

Notice that light has been replaced with sound.

4. REGEXP_SUBSTR ()

REGEXP_SUBSTR (x, pattern [, start [, occurrence [, match_option]) is used to find substrings in x that match pattern, and the starting position is specified by start.

The following query uses the REGEXP_SUBSTR function to return a substring that matches the regular expression l [[: alpha:]] {4}:

SELECTREGEXP_SUBSTR ('But, soft! What light through yonder window breaks?','l [[: alpha:]] {4}') AS resultFROM dual;RESUL-light

5. REGEXP_COUNT ()

REGEXP_COUNT () is a new function added to Oracle Database 11g. REGEXP_COUNT (x, pattern [, start [, match_option]]) is used to find pattern in x and returns the number of times pattern appears in x. You can provide an optional parameter, start, indicating which character you want to start looking for pattern in x, or you can provide an optional match_option string indicating matching options.

The following query uses the REGEXP_COUNT function to return the number of times the regular expression s [[: alpha:]] {3} appears:

SELECTREGEXP_COUNT ('But, soft! What light through yonder window softly breaks?','s [[: alpha:]] {3}') AS resultFROM dual; RESULT-2

Notice that the return result is 2, which indicates that the regular expression has two matches in the supplied string.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.