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

This article mainly introduces "how to master oracle regular expressions". In daily operation, I believe many people have doubts about how to master oracle regular expressions. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "how to master oracle regular expressions". Next, please follow the editor to study!

1. Regular expression specification

(1)。 Matching character

Character class matching example\ d matches 72 from any number\ d\ d of 0mur9, but does not match aa or 7a\ D any non-numeric character\ D\ D\ D matches abc, but does not match 123\ w any word character, including Akashi Zmaine 0-9 and underscore\ w\ w\ w match Ab-2, but does not match ∑ £$% * or Ab_@ any non-word character\ W match @ But does not match any of the a\ s white space characters, including tabs, line feeds, carriage returns, page feeds, and vertical tabs matches all traditional white space characters\ S in HTML,XML and other standard definitions any character other than a non-white space character, such as A%&g3 Wait. Any character matches any character except the newline character unless the MultiLine precedent [...] Any character [abc] in parentheses will match a single character, and acentine b or c. [amurz] will match any character from a to z [^...] Any character that is not in parentheses [^ abc] will match a single character other than a, b, c. It can be a meme b or A, B, C [Amurz] will match any character that does not belong to a Muz, but can match all uppercase letters

(2)。 Repetitive character

The repetition character contains meaning example {n} matches the previous character n times x {2} matches xx, but does not match x or xxx {n,} matches the previous character at least n times x {2} matches 2 or more x, such as xxx,xxx.. {n ~ m} matches the previous character at least n times, at most m times. If n is, this parameter is an optional parameter x {2pm 4} that matches xx,xxx,xxxx but does not match xxxxx? Matches the previous character number or 1 time, which is essentially optional x? Match x or zero x + matches the previous character times or times x + matches x or xx or any number of x * matches the previous character times or more times x * matches 0Pol 1 or more x

(3)。 Positioning character

The pattern that follows the positioning character description ^ must be at the beginning of the string or, if it is a multiline string, at the beginning of the line. For multiline text (a string containing carriage returns), the mode before the multiline flag $must be at the end of the string, if it is a multiline string, the pattern that must be in front of the end of the line\ A must be at the beginning of the string, the pattern before the multiline flag\ z must be at the end of the string, and the pattern before ignoring the multiline flag\ Z must be at the end of the string Or in front of a newline character\ b matches a word boundary, that is, the point between a word character and a non-word character. Remember that a word character is a character in [a-zA-Z0-9]. At the beginning of a word\ B matches the boundary position of a non-word character, not the beginning of a word

(4)。 Grouping character

Group character definition example () this character can combine the characters matched by the pattern in parentheses, which is a capture group, that is to say, the character of the pattern match is the ExplicitCapture option finally set-- by default, the character is not part of the match. The input string is: ABC1DEF2XY matches 3 characters from A to Z and a regular expression of 1 number: ([Amurz] {3}\ d) will produce two matches: Match 1=ABC1 Match2 = DEF2 matches one group at a time: the first group of Match2 = the first group of ABC;Match3 = DEF has a back reference, and you can access the group through its number in the regular expression and C# and class Group,GroupCollection. If the ExplicitCapture option is set, you cannot use the content captured by the group

(5)。 Character cluster:

[: alpha:] any letter.

[[: digit:]] any number.

[[: alnum:]] any letter or number.

[[: space:]] any white character.

[[: upper:]] any capital letter.

[[: lower:]] any lowercase letter.

[[: punct:]] any punctuation.

[[: xdigit:]] any hexadecimal number is equivalent to [0-9a-fA-F].

(6)。 Priority

Escape character

(), (?), (?), [] parentheses and square brackets

*, +,?, {n}, {n,}, {nrecom} qualifier

^, $, anymetacharacter location and order

Or operation

two。 Common function

(1). Regexp_like (expression, regexp)

The return value is a Boolean. If the first parameter matches the regular expression represented by the second parameter, true is returned, otherwise false is returned.

(2). Regexp_instr (expression, regexp, startindex, times)

Returns the location of the matching string found.

(3) regexp_substr (expression, regexp)

Returns the substring in the first string parameter that matches the second regular expression parameter.

(4). Regexp_replace (expression, regexp, replacement)

Replace the matching part of expression by regexp with replacement.

REGEXP_COUNT (source_char, pattern [, position [, match_param]])

REGEXP_COUNT returns the number of times pattern appears in the source_char string. If no match is found, the function returns 0. The position variable tells Oracle where to start the search in the source string. Each time a pattern appears after the start position, the count result is increased by 1.

The match_param variable supports the following values:

'i' is used for case-insensitive matching

'c'is used for case-sensitive matching

'n 'allows a period (.) Match newline characters as wildcards. If this parameter is omitted, the period will not match the newline character

'm 'treats the source string as multiple lines. That is, Oracle regards ^ and $as the beginning and end of any line anywhere in the source string, rather than just the beginning or end of the entire source string. If this parameter is omitted, Oracle treats the source string as one line.

'x' ignores space characters. By default, space characters match themselves.

3. Example:

(1)。 Find a record with a 4-digit id

Select data_object_id,object_name from HH where regexp_like (data_object_id,' ^ [[: digit:]] {3} $')

Select data_object_id,object_name from HH where regexp_like (data_object_id,' ^\ d {3} $')

(2)。 Find records with object names in English and underscores

Select object_name from HH where regexp_like (object_name,' ^ [a murz | Amurz | _] * $')

(3)。 Records with object names starting with an or A

Select object_name from HH where regexp_like (object_name,' ^ [a | A]')

(4)。 The name of the search object is all English and ends in N

Select object_name from HH where regexp_like (object_name,' ^ [Amurz | Amurz] * Nissan')

(5)。 Find employee information that starts with a non-number

Select object_name from HH where regexp_like (object_name,' ^ [^\ d]')

(6)。 Find the location of the first special character in the object name

Select object_name,regexp_instr (object_name,' [^ [: alnum:]]', 1) from HH

(7)。 Starting with the third character, find the position of the second non-numeric character in the employee number

Select object_name,regexp_instr (object_name,' [^ [: alnum:]]', 3) from HH

(8)。 Returns a substring that starts with the second character and whose object name begins with $and ends with an L

Select object_name,regexp_substr (object_name,'L.*\ $$', 2) from HH

(9)。 Replace all non-alphabetic characters in the object name with "A"

Select regexp_replace (object_name,' [^ amurz | Amurz]','A') from HH

(10)。 The number of occurrences of was in the query string, ignoring case.

Select regexp_count ('THE PRO-NIECE WAS BORN TODAY, SO excitations,' Was', 1') from dual

At this point, the study of "how to master oracle regular expressions" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.