How do I only have to input data once?

April 12, 2022

I am currently using the regexp_subtr function to take a string of data (user input) and convert it into a list. Is there a way I can make it so I don’t have to input the data twice. This is what I currently have:

select regexp_substr((WITH X AS (SELECT ('&EmployeeID') a FROM DUAL) SELECT REPLACE(X.a,' ',',') FROM X),'[^,]+', 1, level) "Employee" 
from dual
connect by regexp_substr ((WITH X AS (SELECT ('&EmployeeID') a FROM DUAL) SELECT REPLACE(X.a,' ',',') FROM X),'[^,]+', 1, level) 
is not null;

>Solution :

Yes, you can clean it up a bit like this:

WITH X AS 
  (SELECT REPLACE('&EmployeeID',' ',',') a FROM DUAL)
select regexp_substr(X.a,'[^,]+', 1, level) "Employee" 
from X
connect by regexp_substr(X.a,'[^,]+', 1, level) is not null;