Split one column into multiple columns in Oracle apps

Hi! In this post, I will show you how to split up one column into multiple columns based on delimiters.

Let's say there is a column called Cardholder_name and it has values in the format last_name, first_name. In order to split them into two separate columns i.e., last_name and first_name separately, below syntax can be used.

Method 1:

SELECT REGEXP_SUBSTR(cardholder_name, '[^,]+', 1, 1) AS last_name, 

                REGEXP_SUBSTR(cardholder_name, '[^,]+', 1, 2) AS first_name,

   FROM xx_cardholder_tbl;

Method 2:

SELECT SUBSTR( cardholder_name, 1, INSTR(cardholder_name, ',')-1 ) last_name,

                SUBSTR( cardholder_name, INSTR(cardholder_name, ',')+1, INSTR(cardholder_name, ',',1,2)-INSTR(cardholder_name, ',')-1 ) first_name

   FROM xx_cardholder_tbl;                         --Here ',' is the delimiter.

Happy learning. Cheers!