1.REGEXP_LIKE(x,pattern) function
The function of the REGEXP_LIKE(x, pattern) function is similar to the like operator, which is used to determine whether the source string matches or contains a substring of the specified pattern. x specifies the source string, pattern is a regular expression string. This function is only available in the where clause.
2. Regular expression rules
(1) Regular expressions are usually composed of ordinary characters and meta characters.
(2) Metacharacter. Match any single character, including spaces, Tab characters, and even line breaks.
(3) A set of metacharacter[] describes values.
--Looking up the user, requiring the last_name to have the beginning of 'B', the second character is one of l,m,n, and the third character is one of a,b,c,u,v,w, and ending with a character in c,d,e, and a substring with a length of 4.
SELECT *
FROM customers
WHERE REGEXP_LIKE(last_name,'B[lmn][abcuvw][cde]');(4) Can be used in [] - express range
(5) Used to define subexpressions, and use metacharacter | to represent or relationships internally.
--Look for the user, requiring the last_name to contain a substring that starts with 'l' followed by 'ue' or ' ack'.
SELECT *
FROM customers
WHERE REGEXP_LIKE(last_name,'l(ue|ack)');(6) The metacharacter {n} means repeating the previous element n times; for example: {2,5} means repeating the previous element 2 to 5 times; {3,} means repeating at least 3 times.
(7) For the limit on the number of repetitions, the following special meta characters can also be used:
* means that the previous element is repeated 0 or more times
? means that the previous element is repeated 0 or 1 time
+ means that the previous element is repeated once or more times
(8) Escape characters\
(9) The ^ symbol in brackets indicates "No", the ^ outside brackets indicates the beginning of the string, and $ indicates the end of the string
(10) \d \D \w \W \s \S etc. represent shortcut symbols
3. Complete usage of REGEXP_LIKE function
REGEXP_LIKE(x,pattern[,match_option])
Determines whether x contains a pattern defined string. The desirable values of match_option are:
‘c’ -- indicates case sensitivity when matching (default option)
‘i’ -- indicates that the match is case-insensitive
‘n’ -- Allow. Match any character or line break
‘m’ --.Do not match line break