Scrpt to get the Nth Occurrence of a string in the String

User below script to get the Nth occurrence of the string within a string separated by any string separator.

Ex.. i have a string like 'CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY' and within this string if i want to split the string for Individual column then use the below script.


SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

Please find below the use and output for the above select statement.

SQL> SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL;

Enter value for v_string: CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY

Enter value for l_comma_position: 1

old   1: SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

new   1: SELECT REGEXP_SUBSTR ('CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY', '[^,]+',

REGEXP_SUB

----------

CREATED_BY

SQL> /

Enter value for v_string: CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY

Enter value for l_comma_position: 2

old   1: SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

new   1: SELECT REGEXP_SUBSTR ('CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY', '[^,]+',

REGEXP_SUBSTR

-------------

CREATION_DATE

SQL> /

Enter value for v_string: CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY

Enter value for l_comma_position: 3

old   1: SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

new   1: SELECT REGEXP_SUBSTR ('CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY', '[^,]+',

REGEXP_SUBSTR('C

----------------

LAST_UPDATE_DATE

SQL> /

Enter value for v_string: CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY

Enter value for l_comma_position: 4

old   1: SELECT REGEXP_SUBSTR ('&v_string', '[^,]+', 1, &l_comma_position) FROM DUAL

new   1: SELECT REGEXP_SUBSTR ('CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY', '[^,]+',

REGEXP_SUBSTR('

---------------

LAST_UPDATED_BY

SQL>