Oracle regular expression function matching mobile phone

This article introduces some usage of oracle Regular Expression functions, including matching. Oracle10g provides the function of using regular expression in queries, it is implemented through various functions that support regular expressions in the where clause.

There are four functions that support regular expressions in ORACLE:

1. REGEXP_LIKE: similar to LIKE

2. REGEXP_INSTR: similar to INSTR

3, REGEXP_SUBSTR: similar to SUBSTR

4. REGEXP_REPLACE: similar to REPLACE

1. metacharacters in Regular Expressions
Example of metacharacters
It indicates that the character to be matched is a special character, constant, or referenced by the latter. (The last match is referenced later) n matches the linefeed.
\ Match
(MATCH (
) Match)
^ Matches the start position of A string. If A is the first character of A string, ^ A matches.
$ Match the end position of the string if B is the last character of the string, $ B matches B
* Match the first character 0 or multiple times. ba * rk can match brk, bark, baark, and so on.
+ Matching the first character once or multiple times ba + rk can match bark, baark, and so on, but cannot match brk, that is, at least once.
? Match the first character 0 or 1 ba? Rk can match bark, brk, and so on, but cannot match baark.
{N} matches the previous character EXACTLY n times, where n is an integer hob {2} it can match hobbit
{N, m} matches the preceding characters at least n times and at most m times. n and m are all integers. hob {2, 3} it can match hobbit or hobbbit.
. Match any single character except null in hob. it. can be any single character, such as hobsit.
(Pattern) in parentheses, pattern is a sub-regular expression that matches the specified pattern. For example, aaa (x | y) can match aaax or aaay.
X | y matches "or" x | y can match x or y
[Abc] can match any single character in abc hello [abc] can match helloa, hellob, helloc
[A-z] can match any single character in the specified range. hell [a-z] can match hello or hellz.
[:] Specifies A character class. It can match any character in the class [: alphanum:]. It can match characters 0-9, a-Z, and A-z.
[: Alpha:] matching characters A-Z and a-z
[: Blank:] can match spaces or the tab key
[: Digit:] numbers 0-9 can be matched
[: Graph:] can match non-null characters
[: Lower:] can match lowercase letters a-z
[: Print:] is similar to [: graph:]. The difference is that [: print:] contains space characters.
[: Punct:] can match punctuation marks., "", etc.
[: Space:] can match all null characters
[: Upper:] can match uppercase letters A-Z
[: Xdigit:] It can match hexadecimal numbers 0-9, A-F, a-f
N is a post reference for the previous match. n is a positive integer (.) 1 that can match two consecutive and identical non-null characters. (.) Can match any single character except null, and 1 repeat the content of the last match, that is, matches the same character again, so it can match two consecutive and identical non-null characters

2. REGEXP_LIKE (x, pattern [, match_option]) is used to find the Regular Expression pattern in x. This function also provides an optional parameter match_option string to indicate the default matching options. The value of match_option is as follows:
'C' indicates that the matching time zone is case sensitive (default );
'I' indicates that the matching is case insensitive;
'N' allows operators that can match any character;
'M' uses x as a string that contains multiple rows.

-- Test Data

The Code is as follows:

Copy code

Create table test (mc varchar2 (60 )); Insert into test values ('20140901 '); Insert into test values ('2017 22113344 '); Insert into test values ('2017 33112244 '); Insert into test values ('2014 44112233 5566 778899 '); Insert into test values ('2014 5511 2233 4466778899 '); Insert into test values ('20140901 '); Insert into test values ('20140901 '); Insert into test values ('20140901 '); Insert into test values ('20140901 '); Insert into test values ('aabbccddee '); Insert into test values ('bbaaaccddee '); Insert into test values ('ccabbddee '); Insert into test values ('ddaabbccee '); Insert into test values ('eeaabbccdd '); Insert into test values ('ab123 '); Insert into test values ('123xy '); Insert into test values ('007ab '); Insert into test values ('abcxy '); Insert into test values ('the final test is how to find duplicate words .'); Commit;

1. REGEXP_LIKE

The Code is as follows:

Copy code

Select * from test where regexp_like (mc, '^ a {1, 3 }'); Select * from test where regexp_like (mc, 'a {1, 3 }'); Select * from test where regexp_like (mc, '^ a. * e $ '); Select * from test where regexp_like (mc, '^ [[: lower:] | [[: digit:]'); Select * from test where regexp_like (mc, '^ [: lower:]'); Select mc FROM test Where REGEXP_LIKE (mc, '[^ [: digit:]'); Select mc FROM test Where REGEXP_LIKE (mc, '^ [^ [: digit:]');

Ii. REGEXP_INSTR

The Code is as follows:	Copy code
Select REGEXP_INSTR (mc, '[[: digit:] $') from test; Select REGEXP_INSTR (mc, '[[: digit:] + $') from test; Select REGEXP_INSTR ('the price is $400. ',' $ [[: digit:] + ') from dual; Select REGEXP_INSTR ('onetwothree ',' [^ [: lower:] ') from dual; Select REGEXP_INSTR (',', '[^,] *') from dual; Select REGEXP_INSTR (',', '[^,]') from dual;

Iii. REGEXP_SUBSTR

The Code is as follows:	Copy code
SELECT REGEXP_SUBSTR (mc, '[a-z] +') FROM test; SELECT REGEXP_SUBSTR (mc, '[0-9] +') FROM test; SELECT REGEXP_SUBSTR ('ababcde', '^ a. * B') FROM DUAL;

Iv. REGEXP_REPLACE

The Code is as follows:	Copy code
Select REGEXP_REPLACE ('Joe Smith ',' () {2,} ',', ') AS RX_REPLACE FROM dual; Select REGEXP_REPLACE ('aa bb CC', '(. *)', '3, 2, 1') FROM dual; SQL> select * from test; ID MC -------------------------------------------------------------------------------- A AAAAA A aaaaa B bbbbb

SQL> select * from test where regexp_like (id, 'B', 'I'); -- case insensitive

ID MC
--------------------------------------------------------------------------------

B bbbbb

# End

The following sections will introduce more knowledge 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. This function can also provide an optional parameter match_option, it can be set to one of the following characters:

'C', indicating that the matching time zone is case sensitive (default option)
'I', indicating that the matching is case insensitive.
'N', allows the use of operators that can match any character
'M', uses x as a string that contains multiple rows
The following query uses the REGEXP_LIKE function to retrieve the customers whose birthdays are between January 1, 1965 and January 1, 1968:

The Code is as follows:	Copy code
SELECT customer_id, first_name, last_name, dob FROM MERs WHERE 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 queries customers whose names start with J or j. Note that the regular expression passed to REGEXP_LIKE () is ^ j, and the matching option is I, which means it is case insensitive. In this example, ^ j can match J or j:

The Code is as follows:	Copy code
SELECT customer_id, first_name, last_name, dob FROM MERs WHERE REGEXP_LIKE (first_name, '^ J',' 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 pattern in x; REGEXP_INSTR () returns the location where pattern appears. The matching position starts from 1.

The following query uses the REGEXP_INSTR function to return the position matching the regular expression l [[: alpha:] {4:

The Code is as follows:	Copy code
SELECT REGEXP_INSTR ('But, soft! What light through yonder window breaks? ', 'L [[: alpha:] {4} ') AS result FROM dual;   RESULT ---------- 17

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

The following query returns the second matching position of the regular expression s [[: alpha:] {3}. The matching position starts from 1:

The Code is as follows:	Copy code
SELECT REGEXP_INSTR ('But, soft! What light through yonder window softly breaks? ', 'S [[: alpha:] {3} ', 1, 2) AS result FROM dual;   RESULT ---------- 45

The following query uses the REGEXP_INSTR function to return the second matching position of the letter o, starting from 10:

The Code is as follows:	Copy code
SELECT REGEXP_INSTR ('But, soft! What light through yonder window breaks? ', 'O', 10, 2) AS result FROM dual;   RESULT ---------- 32

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

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

The Code is as follows:	Copy code
SELECT REGEXP_REPLACE ('But, soft! What light through yonder window breaks? ', 'L [[: alpha:] {4} ', 'sound') AS result FROM dual;   RESULT

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

But, soft! What sound through yonder window breaks?

Note that light has been replaced with sound.

4. REGEXP_SUBSTR ()
REGEXP_SUBSTR (x, pattern [, start [, occurrence [, match_option]) is used to find the substring matching pattern in x, and the start position is specified by start.

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

The Code is as follows:	Copy code
SELECT REGEXP_SUBSTR ('But, soft! What light through yonder window breaks? ', 'L [[: alpha:] {4} ') AS result FROM dual;   RESUL ----- Light

5. REGEXP_COUNT ()
REGEXP_COUNT () is a newly added function of Oracle Database11g. REGEXP_COUNT (x, pattern [, start [, match_option]) is used to search for pattern in x and returns the number of times that pattern appears in x. The optional start parameter can be provided to indicate the character from which pattern is to be searched. The optional match_option string can also be provided to indicate matching options.

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

The Code is as follows:	Copy code
SELECT REGEXP_COUNT ('But, soft! What light through yonder window softly breaks? ', 'S [[: alpha:] {3} ') AS result FROM dual;   RESULT ---------- 2

Note that the returned result is 2, which indicates that the regular expression matches the provided string twice.

• Oracle Regular Expression matches the phone number
End number 4: ([0123456789]) 111 $ For example: 13498212222, 13613431111

SQL: select * from tb_phone where REGEXP_LIKE (phone_no, '([0123456789]) 111 $ ')

Tail four consecutive: (0123 | 1234 | 2345 | 3456 | 4567 | 5678 | 6789) $ For example: 13576531234, 13623432345

Tail number goes down four times: (9876 | 8765 | 7654 | 6543 | 5432 | 4321 | 3210) $ For example: 13512329876, 13676987654

End number 00XX: 00 [[: digit:] [[: digit:] $ For example: 13512320023, 13512320035

AABB :( [[: digit:]) 1 ([: digit:]) 2 $ For example: 13567545566

ABAB :( [[: digit:] {2}) 1 $ For example: 13545341212

End number AAAB :( [[: digit:]) 11 [[: digit:] $ For example: 13564326667