Use SQL statements to separate strings

-- Use the regular expression with t as (select 'guangdong, Guangxi, Hainan, Guizhou, Yunnan 'str from dual) SELECT REGEXP_SUBSTR (str,' [^,] + ', 1, ROWNUM) province FROM tCONNECT by rownum <= LENGTH (str)-LENGTH (REPLACE (str, ',') + 1; -- do not use regular with t as (select 'guangdong, guangxi, Hainan, Guizhou, Yunnan 'str from dual) SELECT substr (str, instr (',' | str, ',', 1, rownum ), instr (str | ', 1, rownum)-instr (', '| str,', ', 1, rownum )) province FROM tCONNECT by rownum <= length (str)-length (replace (str, ',', '') + 1;

PROVINCE ------------------------------ Guangdong, Guangxi, Hainan, Guizhou, Yunnan

Substr (string, start_position, [length]) to obtain the string with the specified start position and length.
String is required. Specifies that a part of the string is to be returned.
Start is required. Specifies where the string starts. Positive number-start a negative number at the specified position of the string-
Starting from the specified position at the end of the string 0-starting from the first character in the string
Length is optional. Specifies the length of the string to be returned. The default value is until the end of the string.
Positive number-return a negative number from the position of the start parameter-return from the end of the string
For example: substr ('this is a test', 6, 2) wocould return 'is'
Substr ('this is a test', 6) wocould return 'is a Test'
Substr ('techonthenet ',-3, 3) wocould return 'net'
Substr ('techonthenet ',-6, 3) wocould return 'the'
Substr ('thisatest ',-4, 2) wocould return 'te'

Instr (string1, string2, start_position, nth_appearance) returns the position of the string to be truncated in the source string. It is retrieved only once, that is, from the start of the character to the end of the character.
String1 source string to be searched in this string.
String2: the string to be searched in string1.
Start_position indicates the position of string1 to start searching. This parameter is optional. If omitted, the default value is 1. The string index starts from 1. If
If this parameter is set to negative and from right to left, the system returns the start index of the string to be searched in the source string.
Nth_appearance indicates the number of string2. this parameter is optional. If omitted, the default value is 1. If it is negative, an error is returned.
Select instr ('aa, bb, cc, dd, ee, ff', ',', 1, 1) from dual; -- 3
Select instr ('aa, bb, cc, dd, ee, ff', ',', 1, 2) from dual; -- 6
Select instr ('aa, bb, cc, dd, ee, ff', ',',-1, 1) from dual; -- 15