REGEXP_SUBSTR function in Oracle), oraclesubstr Function

REGEXP_SUBSTR function (convert) and oraclesubstr function in Oracle

REGEXP_SUBSTR function in Oracle

Instructions for using the REGEXP_SUBSTR function in Oracle:

The questions are as follows:
In oracle, a statement is used to split '17, 20,23' into a set of '17', '20', and '23.

The format of the REGEXP_SUBSTR function is as follows:
Function REGEXP_SUBSTR (String, pattern, position, occurrence, modifier)
_ Srcstr: string for Regular Expression Processing
_ Pattern: Regular Expression for matching
_ Position: Start position. The regular expression matches the number of characters starting from (default value: 1)
_ Occurrence: identifies the matching groups. The default value is 1.
_ Modifier: mode ('I' is case-insensitive for retrieval; 'C' is case-sensitive for retrieval. The default value is 'C '.)

1. query the first value after regular expression segmentation, that is, 17

SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,1,'i') AS STR FROM DUAL; 

Result:
STR
-----
17

2. query the last value after regular expression segmentation, that is, 23

SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,3,'i') AS STR FROM DUAL; 

Result:
STR
----
23

3. Obtain a column with multiple values to display the results in multiple rows.

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=7; 

Result:
LEVEL
----
1
2
3
4
5
6
7

4. Associate the occurrence of the above REGEXP_SUBSTR

SELECT NVL(REGEXP_SUBSTR('17,20,23', '[^,]+', 1, LEVEL, 'i'), 'NULL') AS STR FROM DUAL CONNECT BY LEVEL <= 7; 

STR
----
17
20
23
NULL
NULL
NULL
NULL

5. Optimize the preceding SQL statement to make the number of generated rows conform to the actual situation.

SELECT REGEXP_SUBSTR('17,20,23', '[^,]+', 1, LEVEL, 'i') AS STR FROM DUAL CONNECT BY LEVEL <= LENGTH('17,20,23') - LENGTH(REGEXP_REPLACE('17,20,23', ',', ''))+1; 

STR
----
17
20
23

Substr function in oracle

Substr (DETAIL_TYPE, 4,1) = '6'
The fourth character of the string to be intercepted is equal to '6 '.
If the problem is added, it should be substr (DETAIL_TYPE)
When the value of DETAIL_TYPE is 1111053,
Substr (DETAIL_TYPE, 1, 6) = 111105
Take six characters from the first part of DETAIL_TYPE.

Oracle row-to-column function

REGEXP_SUBSTR is the result of the ORACLE function that has been verified by SELECT '1', REGEXP_SUBSTR ('a, B ',' [^,] + ', 1, LEVEL,' I ') as str from dual connect by level <= LENGTH ('a, B ')-LENGTH (REGEXP_REPLACE ('a, B', ',', '') + 1;