Oracle Regular Expressions

Demand:

Match the phone number, the first bit can be +, can not +, the back of all if the number, such as:

+861359415665

8613659558555

1356856455

are legal.

+aa156945555

aa1359556666

Aaddssdfdfsd

are not legal.

Regular:

[SQL]View Plaincopyprint?

1. Sql> SELECT * from DUAL WHERE regexp_like (' +333333 ',' ^[\+]*[[:d igit:]]+ '); --the + escaped or not escaped, the result is the same
3. DUMMY
4. -----
5. X

[SQL]View Plaincopyprint?

1. Sql> SELECT * from DUAL WHERE regexp_like (' aa333333 ',' ^[+]*[[:d igit:]]+ ');
3. DUMMY
4. -----

Explain:

1.^ represents the beginning, * indicates that there are 0 or more occurrences, + indicates occurrences 1 or more times, [:d Igit:] represents 0-9 of the pure number (and also the $ represents with what end, if it is [[:d igit:]]+$ represents a number ending). The meaning of this regular is:

Start with + 0 or more times, followed by one or more occurrences of the number (i.e. must have a number).

In a 2.dual table, there is always only 1 rows of records. There is a record in the dual, which proves that the where condition is established and the other is not.

A previous error has been written:

[SQL]View Plaincopyprint?

1. [\+]*[[:d igit:]]+

Note that there is only one less ^ that represents the start symbol. By missing this symbol, the meaning of this regular is:

+ appears 0 or more times (ie + can appear, can not appear!!) ), followed by a number that appears 1 or more times. Before already + can appear 0 times, prove no + also can, then is as long as the string has the number (+aa111a,aass11111 ...), this is constant set up, the mistake is deep!!

Application of the Oracle regular expression by Wenzhou--ranking

In Oracle, there are four functions available for regular expressions, namely Regexp_like, Regexp_substr, Regexp_instr, and Regexp_replace. This is a flexible application in our Oracle 10g.

First of all, to briefly introduce the contents of the regular expression, the regular expression is the text content of fast query, in the Linux application is more, first, the start and end of the line "^" This character is to find only the beginning of the content. The "$" character finds only the end of the line. Next is "^" can also be used as an exclusion characters. Or use an example to make a demonstration a little bit clearer.

Here I use regexp_like this function to do, so that we can usually use more.

SELECT * FROM test_table

where Regexp_like (field_1, ' ^1234 ')

This means that the string with the beginning of 1234 is not a match. This is the same way as the like.

SELECT * FROM test_table

where Regexp_like (field_1, ' ^[12]234 ')

There's one more [] here to make a stand-alone character, which is said to start with 1 or 2, and then the 234 character will be matched.

SELECT * FROM test_table

where Regexp_like (Field_1, ' ^ (Ouyang | li) Small two ')

Here we can express, this query a surname is Ouyang or Lee, the name is small two string. Here's one more () This is done as a string to write the way to [] exactly corresponds.

There's another "|" here. To express or mean.

SELECT * FROM test_table

where Regexp_like (field_1, ' ^ Lee [small]* two ')

Here we can query Li Xiao or Lee, or Li Xiao, can, here we need to talk about is [] with a *, this is the 0~ infinite large characters to match. This [] we can also add a "+" to indicate 1~ Infinity character to match, can also be more accurate, after [] {1,3} Here is a match of 1 to 3 identical characters. There's another "?" means 1 or 0.

SELECT * FROM test_table

where Regexp_like (Field_1, ' Lee [^ small] two ')

Here we can find the surname Lee, but the second word is not the word "small".

SELECT * FROM test_table

where Regexp_like (Field_1, ' [0-9] ')

Here is a string that represents our query string containing 0-9 of the number.

SELECT * FROM test_table

where Regexp_like (Field_1, ' [A-z] ')

Here is a string representing our query string containing a-Z lowercase letter.

SELECT * FROM test_table

where Regexp_like (Field_1, ' [A-z] ')

Here is a string representing all the letters of our query string containing a-Z.

SELECT * FROM test_table

where Regexp_like (name, ' [[: Alpha:]] ')

Here is a query that matches any letter and also includes Chinese characters

SELECT * FROM test_table

where Regexp_like (name, ' [[: Alnum:]] ')

Here is a query that matches any letter and number

SELECT * FROM test_table

where Regexp_like (name, ' [[:d igit:]]

Here is a query that matches any number

Select * FROM test_table

Where regexp_like (name, ' of ', ' I ')

Here is the case insensitive

Select * FROM test_table

Where regexp_like (name, ' ^[0-9]{1,3}.[ 0-9]{1,3}. [0-9] {1,3}. [0-9] {1,3}$ ')

So we can query whether the IP format

Next introduce REGEXP_SUBSTR

This is also a very useful function.

Regexp_substr the same as the SUBSTR function, returning the truncated substring

Regexp_substr (srcstr, pattern [, position [, occurrence [, match_option]])

Note:

Srcstr Source String

Pattern Regular Expression Style

Position start Match character position

Occurrence number of matches occurred

Match_option Matching options (case sensitive)

SELECT regexp_substr (' 1psn/231_3253/abc ', ' [[: alnum:]]+ ') from dual;

Output:1psn

[[: alnum:]]+ matches 1 or more alphabetic or numeric characters

SELECT regexp_substr (' 1psn/231_3253/abc ', ' [[: alnum:]]+ ', 1, 2) from dual;

output:231

Two more parameters compared to the above example

1 to find a match starting from the first character of the source string

2 indicates the 2nd occurrence of the string (the default value is "1", as in the previous example)

Select Regexp_substr (' @@/231_3253/abc ', ' @*[[:alnum:]]+ ') from dual;

output:231

@* means matching 0 or more @

[[: alnum:]]+ matches 1 or more alphabetic or numeric characters

Note: The difference between "+" and "*" needs to be distinguished

Select Regexp_substr (' [Email protected]/231_3253/abc ', ' @+[[:alnum:]]* ') from dual;

Output: @

@+ means matching 1 or more @

[[: alnum:]]* matches 0 or more alphabetic or numeric characters

Select Regexp_substr (' [Email protected]/231_3253/abc ', ' @+[[:alnum:]]+ ') from dual;

Output:null

@+ means matching 1 or more @

[[: alnum:]]+ matches 1 or more alphabetic or numeric characters

Select Regexp_substr (' @1psn/231_3253/abc125 ', ' [[:d igit:]]+$ ') from dual;

output:125

[[:d igit:]]+$ represents a character that matches 1 or more digits end

Select Regexp_substr (' [Email protected]/231_3253/abc ', ' @+[[:alnum:]]+ ') from dual;

Output:null

@+ means matching 1 or more @

[[: alnum:]]+ matches 1 or more alphabetic or numeric characters

Select Regexp_substr (' @1psn/231_3253/abc125 ', ' [[:d igit:]]+$ ') from dual;

output:125

[[:d igit:]]+$ represents a character that matches 1 or more digits end

Select Regexp_substr (' @1psn/231_3253/abc ', ' [^[:d igit:]]+$ ') from dual;

Output:/ABC

[^[:d igit:]]+$ to match 1 or more characters that are not the end of a number

Select Regexp_substr (' [email protected] ', ' [^@]+ ') from dual;

Output:tom_kyte

[^@]+ = matches 1 or more characters that are not ' @ '

Select Regexp_substr (' 1psn/231_3253/abc ', ' [[: alnum:]]* ', ')

from dual;

Output:null

[[: alnum:]]* matches 0 or more alphabetic or numeric characters

Note: Because it matches 0 or more, the 2nd match here is "/" (matched 0 times) instead of "231", so the result is "Null"

Here we sometimes query string ASDFAFD<MAIN>DAFDA here we're going to take out <main> this string

Select regexp_substr (' asdfafd<main>dafda ', ' <[^>]+> ') from dual

Output: <main>

Here we go to a ^> in the middle of the <> so after matching <, in the backward query to ensure that the match to > before the time is no longer, otherwise there will be an error situation.

Select regexp_substr (' asdfafd<main>da>fda ', ' <[^<]+> ') from dual

Output: <main>da>

In this example, we are still da> after <main>, so that if we do not add ^>, the regular expression will go back to match until the last > is up, so there will be a bias

The biographies line that is commonly used to implement strings

Select Regexp_substr (' 123;234;345;456;567;678;789 ', ' [^;] + ', 1,rownum) from dual

Connect by rownum <= Length (' 123;234;345;456;567;678;789 ')-Length (replace (' 123;234;345;456;567;678;789 ', '; ')) +1

Here length here is how many ";" is first obtained, and then through the Connect by rownum way to do a line of multi-line operations, after becoming more than one line, you can use REGEXP_SUBSTR to take the operation of the string

Then the previous example

A,b,c,d,e,d,f,a,n A string like this, we are now going to remove some of the string, so the result is a,b,c,d,e,f,n minus the two strings of D and a.

Select Wm_concat (New_row) from (

SELECT distinct regexp_substr (' a,b,c,d,e,d,f,a,n ', ' [^,]+ ', 1,rownum) New_row from dual

Connect by Rownum<=length (' A,b,c,d,e,d,f,a,n ')-length (replace (' a,b,c,d,e,d,f,a,n ', ', ')))

By turning into multiple lines, then using distinct to remove duplicates, and then we will use Wm_concat to string merge to complete.

Another example of IP format conversion, our general IP format is 12.19.168.27 now to less than 3 bits of the top 0, the result is 012.019.168.027

Select Wm_concat (New_value) from (

Select

Lpad (Regexp_substr (' 12.19.168.27 ', ' [^.] + ', 1,rownum), 3, ' 0 ') new_value,rownum

From dual

Connect by Rownum<5

ORDER by RowNum)

To verify that the IP is the correct number

Select COUNT (*) from (

Select

Lpad (Regexp_substr (' 12.19.168.27 ', ' [^.] + ', 1,rownum), 3, ' 0 ') new_value,rownum

From dual

Connect by Rownum<5)

where New_value>=0 and new_value<256

Having Count (*) =4

To convert an IP string format into a digital IP

Select SUM (New_value*power (256,4-RM)) from (

Select Regexp_substr (' 12.19.168.27 ', ' [^.] + ', 1,rownum) New_value,rownum RM from dual

Connect by rownum<=4

)

Next, we introduce a regexp_instr function

The REGEXP_INSTR function uses regular expressions to return the start and end points of the search pattern. The syntax for REGEXP_INSTR is as follows. REGEXP_INSTR Returns an integer that indicates the starting or ending position of the search pattern, and returns 0 if no matching value is found.

Grammar:

2.regexp_instr the same as the InStr function, returns the string position

Regexp_instr (srcstr, pattern [, position [, occurrence [, return_option [, Match_option]]])

Like Regexp_substr, it also has variable pattern, position (starting position), occurrence, and match_parameter; Here is the main introduction of the new parameter return_option role, It allows the user to tell Oracle what to return when the pattern appears.

Select regexp_instr (' ASDFAFD<MAIN>DA>FDA ', ' SD ') from dual

Output:2

Here to query the location of the SD, this and InStr are in the same

Select regexp_instr (' asdfafd<main>da>fda ', ' da ', up) from dual

This is where the second da appears.

And we often encounter a situation is to query a field, if it is equal to "Shanghai" or "Beijing" or we are in Wenzhou as a big city, and others written in small cities, we generally consider the use of decode this way

Select decode (' Shanghai ', ' Shanghai ', ' big city ', ' Beijing ', ' big city ', ' Wenzhou ', ' big city ', ' small city ') from dual

Only two we may think SQL is not very lengthy, if there are four or five, it is a bit long, here to use REGEXP_INSTR can be a lot of to operate

Select Decode (regexp_instr (' Beijing ', ' ^ (Shanghai | beijing | wenzhou) '), 0, ' small city ', ' big city ') from dual

By the REGEXP_INSTR mismatch when the condition is 0, so that you can complete the

The last function Regexp_replace

The Regexp_replace function replaces a value in a string with a different value. For example, you can replace each occurrence of a letter with a matching number. The format of the Regexp_replace is as follows

Grammar:

4.regexp_replace the same as the Replace function, replacing the contents of the characters in the original string

Regexp_replace (srcstr, pattern [, replacestr [, Position [, occurrence [, match_option]]])

This replacement function is also a very useful one.

As we have a string Adfadfa (main) Next now we are going to replace () with a <>, and here we may want to do it with replace, but what we are doing now is (must have) such (after) we replace the <>

Select Regexp_replace (' Adfadfa (main) Next ', ' (\ () ([^\)]*) (\)) ', ' <\2> ') from dual

Output:adfadfa <main> Next

Here is also a \ as escape character.

Another example of IP format conversion, our general IP format is 12.19.168.27 now to less than 3 bits of the top 0, the result is 012.019.168.027

Select Regexp_replace (

Regexp_replace (' 12.19.168.27 ', ' ([0-9]{1,3}). ( [0-9] {1,3}). ([0-9]{1,3}). ([0-9]{1,3}) ',

' 00\1.00\2.00\3.00\4 '),

' ([0-9]*) ([0-9]{3}\.) ([0-9]*) ([0-9]{3}\.) ([0-9]*) ([0-9]{3}\.) ([0-9]*) ([0-9]{3}$) ', ' \2\4\6\8 ')

From dual

output:012.019.168.027

Here I am divided into two steps to operate, Regexp_replace (' 12.19.168.27 ', ' ([0-9]{1,3}). [0-9] {1,3}). ([0-9]{1,3}). ([0-9]{1,3}) ',

' 00\1.00\2.00\3.00\4 ') I first let each small string do add 0, so that each string will be greater than 3, and then

' ([0-9]*) ([0-9]{3}\.) ([0-9]*) ([0-9]{3}\.) ([0-9]*) ([0-9]{3}\.) ([0-9]*) ([0-9]{3}$) ', ' \2\4\6\8 ')

This whole string is divided into 8 paragraphs so that we can just 2, 4, 6, 8 of these four segments.

In the following example, a space character is inserted between each of the two characters

SELECT regexp_replace (' YAHOO ', ' (.) ', ' \1 ') as output from dual;

Output:y A H o O

This is done in a circular way, it's fine.

Select Regexp_replace (

Regexp_replace (' 12.19.168.27 ', ' ([^.] +) '

, ' 00\1 ')

, ' ([^.] *)([^.] {3}) ', ' \2 ')

From dual

And then that, we can operate the Replace loop substitution method.

Examples of identity card constraints

ALTER TABLE PUB_EMPLOYEE_EXP
  Add constraint pub_employee_exp ID format error
  Check ((Regexp_like (Idcard, ' [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] ')
Or
Regexp_like (Idcard, ' [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9, X] '))
and length (Idcard) in (15,18)
and Trim (Idcard) =idcard
and InStr (IDCARD,CHR (10)) =0);

This is a full version, including length, space, line breaks are limited

Oracle Regular Expressions