How to use regular expressions in Oracle

Editor to share with you how to use regular expressions in Oracle, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

Oracle can't use regular expressions without these four functions: regexp_like, regexp_substr, regexp_instr, and regexp_replace.

Regexp_like

This function can only be used for conditional expressions, similar to like, but using regular expressions for matching

/ / query all records that contain lowercase letters or numbers. Select * from fzq where regexp_like (value,' ^ ([amurz] + | [0-9] +) $')

Regexp_substr

This function, similar to substr, is used to pick up the character substring described by the regular expression of the symbol. The function is defined as follows.

Function REGEXP_SUBSTR (String, pattern, position, occurrence, modifier)-string entered by String-pattern regular expression-position identifies the number of characters from which the regular expression matches. (default is 1)-occurrence identifies the matching group. (default is 1)-modifier value range: I: case insensitive; c: case sensitive; n: period. Does not match newline symbols; m: multiline mode; x: extended mode, ignoring white space characters in regular expressions.

Here are some examples

-- retrieve the middle numeral SELECT REGEXP_SUBSTR (a _ line'[0-9] +') FROM test_reg_substr WHERE REGEXP_LIKE (a _ line'[0-9] +') -- retrieve the middle number (start with the first letter and find the second matching item) SELECT NVL (REGEXP_SUBSTR (a _ () _ () _ (0-9) +', 1, 2),'-) AS a FROM test_reg_substr WHERE REGEXP_LIKE (_ _)

Regexp_instr

Similar to instr, this function is used to calibrate the start position of a character substring that conforms to a regular expression. The syntax of the REGEXP_INSTR function in the Oracle database is

REGEXP_INSTR (source_char, pattern [, position [, occurrence [, return_option [, match_parameter])-character expressions for source_char search values-pattern regular expressions-position optional. Search the start position in the string. If omitted, the default is 1, which is the first position in the string. -occurrence is optional. It is the nth matching position in the pattern string. If omitted, the default is 1. -return_option optionally specifies the position returned by Oracle, and 0 then Oracle returns the position of the first character that appears. This is the default, and 1 Oracle returns the position that occurs after the character. -match_parameter value range: I: case insensitive; c: case sensitive; n: period. Does not match newline symbols; m: multiline mode; x: extended mode, ignoring white space characters in regular expressions.

Here are some examples

-- find the location of the first "e" word in the string-- return 2SELECT REGEXP_INSTR ('hello itmyhome', 'e') FROM dual -- "1" for the start position "2" searches for the second match, "0" is return_option-- returns the first character position "c" that appears is case-sensitive, so it returns 13SELECT REGEXP_INSTR ('my is itMyhome', 'with', 1, 1, 2, 0,' c') FROM dual;--SELECT REGEXP_INSTR ('World filled with love',' with', 1, 1, 0,'i') FROM dual -- match multiple alternative SELECT REGEXP_INSTR ('Itmyhome', 'a | I | o | e | u') FROM dual

Regexp_replace

This function is similar to replace and is used to replace strings that conform to regular expressions. The syntax of the REGEXP_REPLACE function in the Oracle database is

REGEXP_REPLACE (source_char, pattern [, replace_string [, position [, occurrence [, match_parameter])-character expressions for source_char search values-pattern regular expressions-replace_string optional. The matching pattern is replaced with the replace_string string. If the replace_string parameter is omitted, all matching patterns are deleted and the result string is returned. -position is optional. Search at the beginning of the string. If omitted, the default is 1. -occurrence it is the nth matching position in the pattern string. If omitted, the default is 1. -match_parameter I: case insensitive; c: case sensitive; n: period. Does not match newline symbols; m: multiline mode; x: extended mode, ignoring white space characters in regular expressions.

Here are some examples

-- string substitution-- luck is my network idSELECT REGEXP_REPLACE ('itmyhome is my network id',' ^ (\ S*)', 'luck') FROM dual;-- this example replaces the specified\ d number with the # character-- Result:' #, #, and # # are numbers in this example'SELECT REGEXP_REPLACE ('2,5, and 10 are numbers in this example','\ dink,'#') FROM dual The above is all the content of the article "how to use regular expressions in Oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!