How does Oracle separate fields using a specified delimiter?

2 years ago
William Carter
1 minute

You can use Oracle’s SUBSTR and INSTR functions to split the field by a specified symbol.

If you have a field named “field” containing a value that needs to be split, you can use the following SQL statement to separate the field by a specified delimiter.

SELECT SUBSTR(field, 1, INSTR(field, '指定符号', 1, 1) - 1) AS first_part,
       SUBSTR(field, INSTR(field, '指定符号', 1, 1) + 1, INSTR(field, '指定符号', 1, 2) - INSTR(field, '指定符号', 1, 1) - 1) AS second_part,
       SUBSTR(field, INSTR(field, '指定符号', 1, 2) + 1) AS third_part
FROM 表名;

Please replace the “field” with your actual field name, the “specified symbol” with the symbol you wish to use, and the “table name” with your actual table name in the SQL statement above.

The above SQL statement splits fields based on specified symbols that appear for the first and second time, then saves the results into columns named “first_part”, “second_part”, and “third_part”. You can adjust the number of splits and the column names based on your specific needs.

bannerAds