CODEWITHSUNDEEP
regexoracle-databasesplitsubstr
Kokirala Sudheer
Kokirala Sudheer

Reputation: 439

REGEXP_SUBSTR Is taking more time for execution in Oracle

I am trying to split a comma separated email string into individual email ids which are comma separated but each email id is enclosed inside single quotation.

My Input is '[email protected],[email protected],[email protected],[email protected]'

My Output Should be: '[email protected]','[email protected]','[email protected]','[email protected]'

I am going to use the output string above in oracle query where condition like...

Where EmailId's in ( '[email protected]','[email protected]','[email protected]','[email protected]');

I am using the following code to achieve this 

 WHERE EMAIL IN 
                    (REGEXP_SUBSTR('[email protected],[email protected],[email protected],[email protected]' ,'[^,]+', 1, LEVEL))
CONNECT BY LEVEL <= LENGTH('[email protected],[email protected],[email protected],[email protected]' ) - LENGTH(REPLACE('[email protected],[email protected],[email protected],[email protected]' , ',', '')) +1;

But the above query taking 60 seconds to return only 16 records. Can any one suggest me the best approach for this...

Upvotes: 1

Views: 2376

Answers (1)

Dba
Dba

Reputation: 6639

Try this,

WHERE email IN (
  select regexp_substr('[email protected],[email protected],[email protected],[email protected]','[^,]+', 1, level) from dual
  connect by regexp_substr('[email protected],[email protected],[email protected],[email protected]', '[^,]+', 1, level) is not null );

Upvotes: 2

Related Questions