How Oracle splits the string REGEXP_SUBSTR with regular expressions

This article mainly introduces how Oracle divides the string REGEXP_SUBSTR through regular expressions. The article is very detailed and has certain reference value. Interested friends must read it!

The REGEXP_SUBSTR function has the following format:

function REGEXP_SUBSTR(string, pattern, position, occurrence, modifier)

string: String to be processed regularly

pattern: regular expression to match

position: starting position, from which character the regular expression matches (default is 1)

occurrence: identifies the number of matching groups, default is 1

modifier: mode ('i 'case-insensitive retrieval;'c'case-sensitive retrieval. Default is 'c')

SELECT REGEXP_SUBSTR('123,,,ABC,!@#,,, ', '[^,]+') FROM DUAL ;

Take the first value after segmentation

Results:123

SELECT REGEXP_SUBSTR('123,,,ABC,!@#,,, ', '[^,]+', 2) FROM DUAL ;

Match from the second character, take the first value after segmentation

results:23

SELECT REGEXP_SUBSTR('123,,,ABC,!@#,,, ', '[^,]+', 1, 2) FROM DUAL ;

Take the second value after segmentation

Result:ABC

SELECT REGEXP_SUBSTR('123,,,ABaC,!@#,,, ', '[^a]+', 1, 2, 'i') FROM DUAL ;

The second value after case insensitive partitioning

results:B

SELECT REGEXP_SUBSTR('123,,,ABaC,!@#,,, ', '[^a]+', 1, 2, 'c') FROM DUAL ;

The second value after case-sensitive partitioning

Result:C,!@#,,

SELECT REGEXP_SUBSTR('123,,,ABC,!@#,,, ', '[^,]+', 1, LEVEL) FROM DUAL CONNECT BY REGEXP_SUBSTR('123,,,ABC,!@#,,, ', '[^,]+', 1, LEVEL) IS NOT NULL;

Take all results after segmentation ( level oracle keyword, indicating query depth, used to implement hierarchical query)

Results:

123

ABC

!@#

The above is "Oracle how to split the string REGEXP_SUBSTR by regular expression" all the content of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!