We might face some scenarios for convert string data into rows. Here you can separate a string into rows by giving the string separator as input. Users can give any character as a separator as the input parameter for the function.
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. It will be useful when you are using the ‘Multi-Select’ field.
Technologies and Tools Used
You have to use the following technologies to string separation
- PL/SQL
Steps to Follow
Step 1: You have to create the below objects.
CREATE OR REPLACE TYPE “DOY_T_TF_ROW” AS OBJECT (
id NUMBER,
col_val VARCHAR2(200)
)
/
CREATE OR REPLACE TYPE “DOY_T_TF_TAB” IS TABLE OF doy_t_tf_row
/
Step 2: Create the below-pipelined function for separating the string into rows.
CREATE OR REPLACE FUNCTION doy_string_seperator(
ip_string IN VARCHAR2,
ip_seperator IN VARCHAR2)
RETURN doy_t_tf_tab PIPELINED
AS
BEGIN
FOR i IN
(SELECT rownum r_no, REGEXP_SUBSTR (ip_string, ‘[^’||ip_seperator||’]+’, 1, LEVEL) COLUMN_VALUE
FROM DUAL
CONNECT BY REGEXP_SUBSTR (ip_string, ‘[^’||ip_seperator||’]+’, 1, LEVEL) IS NOT NULL
)
LOOP
PIPE ROW ( doy_t_tf_row(i.r_no, i.COLUMN_VALUE) );
END LOOP;
RETURN;
END;
/
Parameter Description:
ip_string: The string needs to be separated.
ip_seperator: The separator character.
Result
eg 1.
Input Parameters:
ip_string => APPLE/ORANGE/GRAPES/MANGO
ip_seperator => / (Slash)
eg 2.
Input Parameters:
ip_string => INDIA IS MY COUNTRY
ip_seperator => (Space)
