regexp_substr Handle CSV Data

REGEXP_SUBSTR(source_string, pattern
[, position [, occurrence
[, match_parameter]]])

1. 'source_string' is a character expression that serves as the search value.
2. 'pattern' is the regular expression. It is usually a text literal.
3. 'position' is a positive integer indicating the character of
4. 'source_string' where Oracle should begin the search. The default position is 1.
5. 'occurrence' is a positive integer indicating which occurrence of pattern in source_string Oracle should search for. The default is 1.
6. 'match_parameter' is a text literal that lets you change the default matching behavior of the function.
7. 'match_parameter' can be a combination of
1. i: to match case insensitively
2. c: to match case sensitively
3. n: to make the dot (.) match new lines as well
4. m: to make ^ and $ match beginning and end of a line in a multiline string

-- match 'call', 'caller', 'called', or 'calling' in the string

SELECT REGEXP_SUBSTR('You have called me too often, she said.','call((ing) (er) (ed))')
FROM dual;
 
Handle  CSV data

select '...'||LTRIM(regexp_substr('I had a dog, he had white fur, his name was Bob.','[^,]+', 1,level) ), level from dual
 connect by
regexp_substr('I had a dog, he had white fur, his name was Bob.', '[^,]+', 1, level) IS NOT NULL


DECLARE
--
p_csv VARCHAR2(1000):= '688646,688563,807141';
p_csv1 VARCHAR2(1000):='27057931H,18025414H,18025415H,27057934H,27057937H,27061344H,27061345H,18025405H,18025408H';
--
CURSOR c
IS
SELECT serial_number
FROM mtl_serial_numbers
WHERE rownum < 12
AND inventory_item_id IN (
select regexp_substr(p_csv,'[^,]+', 1,level)
from dual
connect by
regexp_substr(p_csv, '[^,]+', 1, level) IS NOT NULL
);

CURSOR c1
IS
SELECT inventory_item_id
FROM mtl_serial_numbers
WHERE 1=1
AND serial_number IN (
select regexp_substr(p_csv1,'[^,]+', 1,level)
from dual
connect by
regexp_substr(p_csv1, '[^,]+', 1, level) IS NOT NULL
);

BEGIN

--
FOR i IN C
LOOP
--
dbms_output.put_line(i.serial_number);
--
END LOOP;
--
FOR i IN C1
LOOP
--
dbms_output.put_line(i.inventory_item_id);
--
END LOOP;
--
END;