Oracle Regular expression 2 includes underscore handling __ large data

l        square brackets Expression

Square Bracket Expression	Description
[[: Alnum:]]	Characters mixed with letters and numbers
[[: Alpha:]]	Alphabetic characters
[[: Cntrl:]]	Control characters
[[:d Igit:]]	numeric characters
[[: Graph:]]	Image characters
[[: Lower:]]	Lowercase alphabetic characters
[[:p rint:]]	Print characters
[[:p UNCT:]]	Punctuation characters
[[: Space:]]	Space characters
[[: Upper:]]	Uppercase characters
[[: Xdigit:]]	hexadecimal numeric characters

L Meta character

Meta character	Description
*。	Match if there are more than 0 occurrences of the preceding string
+	Match if there are more than 1 occurrences of the preceding string
{m}	M is an integer. It finds exactly m occurrences of the given subexpression in the text
{n}?	The previous string only occurs once when the match
{m,}	M is an integer. It finds at least m occurrences of the given child expression in the text
{N,}?	Matches the preceding string at least n times
{M,n}	M and n are integers. It finds the M to n occurrences of a given child expression in the text
{N,m}	Matches the preceding string to at least n times, but not more than m times
C	Query operations are case sensitive
I	Query operation does not distinguish between flood writing
M	A query on a multiline string that allows you to match the start of a string with a (^) pattern when the source string contains more than a row
N	Usually match a single character or a new row
X	You need to ignore the whitespace characters in the regular expression, using the parameter ' X '
\a	Matches the head of a string, not the beginning of the line, so multiple lines of string cannot match each row
\d	Match any number of characters
\d	Match any non-numeric character
\s	Match any white space character
\s	Match any non-whitespace character
\w	Matches any character and number. The difference between the character and [: Alnum:] is \w including the underscore character
\w	Match any non-empty string
*	Wildcard characters. Finds records that contain 0 or more occurrences of a given subexpression in the text
+	Finds records that contain 1 or more occurrences of a given subexpression in the text
。	Find the record in the text that contains 0 or 1 occurrences of a given child expression
.	Match any character in the text
^	Anchor. If the expression after the character appears at the beginning of the line, the match succeeds
$	Anchor. If the expression after the character appears at the beginning of the line, the match succeeds
|	Delimiters, using the same method and OR
(....)	Grouping sub-expressions

L -Regular expression operators and functions

a) Regexp_substr

Regexp_substr (srcstr, pattern, position, occurrence, modifier)

__SRCSTR: Retrieving strings

__pattern: Matching mode

__position: Search Srcstr starting position (defaults to 1)

__occurrence: Search for the first occurrence of a matching pattern string (default 1)

__modifier: Retrieval mode (' I ' is case-insensitive for retrieval; ' C ' is case-sensitive for retrieval. The default is ' C '. ）

eg

Sql> Select Regexp_substr (' http://blog.chinaunix.net/u/30637/showart_1009927.html ', ' [0-9]+ ') from dual; Regexp_substr (' HTTP://BLOG.CHI ------------------------------ 30637 Sql> Select Regexp_substr (' http://blog.chinaunix.net/u/30637/showart_1009927.html ', ' [0-9]+ ', 1) from dual; Regexp_substr (' HTTP://BLOG.CHI ------------------------------ 30637 Sql> Select Regexp_substr (' http://blog.chinaunix.net/u/30637/showart_1009927.html ', ' [0-9]+ ', km) from dual; Regexp_substr (' HTTP://BLOG.CHI ------------------------------ 1009927 Sql> Select Regexp_substr (' http://blog.chinaunix.net/u/30637/showart_1009927.html ', ' [0-9]+ ', 1, 2) from dual; Regexp_substr (' HTTP://BLOG.CHI ------------------------------ 1009927 Sql> Select Regexp_substr (' http://blog.chinaunix.net/u/30637/showart_1009927.html ', ' [a-z_0-9]+ ', km) from dual; Regexp_substr (' HTTP://BLOG.CHI ------------------------------ showart_1009927 Sql> Select Regexp_substr (' http://blog.chinaunix.net/u/30637/Showart_1009927.html ', ' [a-z_0-9]+ ', km) from dual; Regexp_substr (' HTTP://BLOG.CHI ------------------------------ howart_1009927 Sql> Select Regexp_substr (' http://blog.chinaunix.net/u/30637/Showart_1009927.html ', ' [a-z_0-9]+ ', 1, ' C ') from Dual Regexp_substr (' HTTP://BLOG.CHI ------------------------------ howart_1009927 Sql> Select Regexp_substr (' http://blog.chinaunix.net/u/30637/Showart_1009927.html ', ' [a-z_0-9]+ ', 1, ' I ') from Dual Regexp_substr (' HTTP://BLOG.CHI ------------------------------ showart_1009927

b) Regexp_instr

REGEXP_INSTR returns the position of characters and strings that match the regular expression. Such as

Sql> Select Regexp_instr (' The ZIP code 80831 is for Falcon, CO ', ' [[:d igit:]]{5} '] regexp_instr from dual;

Regexp_instr

------------

14

c) Regexp_replace

Regexp_replace, like the Replace function, provides a way to modify a string that matches a given regular expression. Functions include correcting spelling errors and formatting text for input and output.

The format of the phone number is: 719-111-1111. The return value using Regex_replacer is:

Sql> Select Regexp_replace (' Reformat the phone number 719-111-1111 ... ',

2 ' [1]? [-.]? (\(? [[:d Igit:]] {3}\)? +[- .]?'

3 | | ' ([[:d igit:]]{3}] [-.]? ([[:d igit:]]{4}] ',

4 ' (\1) \2-\3 ') regexp_replace

5 from dual;

Regexp_replace

---------------------------------------------

Reformat the phone number (719) 111-1111 ...

S

d) Regexp_like

The regexp_like operator is similar to the LIKE operator but is more powerful because it supports the use of matching the regular expression with the text. The syntax is as follows:

Regexp_like (source_string, pattern, match_parameter)

A source_string can be a literal string, or, if the string in the preceding example, a variable or column containing some string. pattern is the regular expression to be matched. Match_parameter is used to specify whether the match is case-sensitive.

Sql> Select ename, Job

2 from EMP

3 where Regexp_like (Job, ' (clerk|analyst) ', ' I ');

Ename JOB

---------- ---------

SMITH Clerk

SCOTT ANALYST

ADAMS Clerk

JAMES Clerk

FORD ANALYST

MILLER Clerk

============================ Personal Use experience =======================================

Select P.port_no,p.display_name, substr (P.port_iden,instr (P.port_iden, '/', -1,1) + 1)
From Tb_port p where p.type_info = ' ADSL '
and Regexp_like (substr (P.port_iden,instr (P.port_iden, '/', -1,1) + 1), ' ^[:p unct:]|[ 0-9]+$ ')
and To_number (substr (P.port_iden,instr (P.port_iden, '/', -1,1) + 1)) <> p.port_no and p.port_no = 0

1. Application of Regexp_like (substr (P.port_iden,instr (P.port_iden, '/', -1,1) + 1), ' ^[:p unct:]|[ 0-9]+$ ') to judge the strings are all numbers

2. substr (P.port_iden,instr (P.port_iden, '/', -1,1) + 1) intercept the character in the string containing '/', followed by the last slash

3. To_number converts character numbers to number type

INSTR

(Source string, target string, starting position, matching ordinal number)

In Oracle/plsql, the InStr function returns the position of the string to be intercepted in the source string. Retrieve only once, that is, from the beginning of the character

Ends at the end of the character.

The syntax is as follows:

InStr (string1, string2 [, Start_position [, Nth_appearance]])

Parameter analysis:

String1

The source string to find in this string.

string2

The string to find in the string1.

Start_position

Represents which location of the string1 to start the lookup. This parameter is optional, if omitted defaults to 1. The string index starts at 1. If this parameter is positive, retrieves from left to right, if this parameter is negative, retrieves from right to left, and returns the starting index of the string to find in the source string.

Nth_appearance

Represents the string2 to find the first occurrence. This parameter is optional, and if omitted, the default is 1. If the system is negative, the error occurs.

Attention:

If String2 is not found in String1, the InStr function returns 0