When we are working with the ‘Multi Select’ field , we will face difficulty to process each separated values. Here you can split a colon-separated string into rows using an apex utility. Assume there is a scenario like you are having data in the form of a colon-separated string and you want to process each value one by one. In this case, you have to convert the colon-separated string into rows, that you have to use in the ‘IN’ clause of a select query.
Technologies and Tools Used
The following technology has been used to achieve the same.
- PL/SQL
- Apex
Steps to follow
Here we are going to split the values into rows.
The following steps explain in detail,
Step 1: Create the below object.
create or replace TYPE “TAB_TYP_VARCHAR” as table of varchar2(4000);
/
Step 2: Create the below pipelined function.
create or replace FUNCTION doy_fn_str_to_tbl (
ip_string IN VARCHAR2
)
RETURN TAB_TYP_VARCHAR PIPELINED
IS
lv_v_temp apex_application_global.vc_arr2;
BEGIN
lv_v_temp := apex_util.string_to_table (ip_string , ‘:’);
FOR j IN 1 .. lv_v_temp.COUNT
LOOP
PIPE ROW (lv_v_temp (j));
END LOOP;
END;
/
Parameter Description:
ip_string : The string needs to be separated.
Output
- select * from table (DOY_FN_STR_TO_TBL(‘APPLE:ORANGE:GRAPES:MANGO’));
ip_string => ‘APPLE:ORANGE:GRAPES:MANGO’
2. select * from table (DOY_FN_STR_TO_TBL(‘DAVID:MIKE:SACHIN:KOHLI’));
ip_string => ‘DAVID:MIKE:SACHIN:KOHLI ‘
