How do I only have to input data once?-CodePudding

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;

CodePudding user response：

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;

CodePudding user response：

Using xmltable with tokenize:

select * 
from xmltable('tokenize(replace(.," ",","),",")'
              passing '&EmployeeID'
              columns s varchar2(100) path '.');

DBFiddle: https://dbfiddle.uk/?rdbms=oracle_21&fiddle=19ab9c444502f6e1fd3bdaa44e04ab27

CodePudding user response：

It appears your client is sqlplus, where the ampersand ('&') is an indication to the client to prompt for user input. You can avoid being re-prompted for the same variable by adding a second ampersand in front of the variable name.

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;