How can Oracle extract a substring based on a comma?

In Oracle, you can use the SUBSTR function and INSTR function to extract a string based on commas.

Here is an example:

Assuming there is a string like this:
str := ‘apple,banana,orange’

You can use the following code to split a string based on commas:

-- 截取第一个逗号之前的字符串
SELECT SUBSTR(str, 1, INSTR(str, ',')-1) FROM dual;

-- 输出结果为 'apple'

-- 截取第一个逗号之后的字符串
SELECT SUBSTR(str, INSTR(str, ',')+1) FROM dual;

-- 输出结果为 'banana,orange'

-- 截取第二个逗号之前的字符串
SELECT SUBSTR(str, INSTR(str, ',', 1, 2)-1) FROM dual;

-- 输出结果为 'banana'

-- 截取第二个逗号之后的字符串
SELECT SUBSTR(str, INSTR(str, ',', 1, 2)+1) FROM dual;

-- 输出结果为 'orange'

The SUBSTR function can be used to specify the starting position and length of the substring to extract. The INSTR function is used to locate the position of a comma.

It is worth noting that ‘dual’ in the above code is a virtual table used for querying when there is no actual table present. You can replace it with the name of the table you want to query based on the actual situation.