CODEWITHSUNDEEP
sqloracle-database
Sushant
Sushant

Reputation: 21

Error: ORA-00907: missing right parenthesis - can you help figure out issue

select regexp_substr((replace(replace(replace(('CA','CO','IL','KS'),chr(40)),chr(41)), chr(39))), '[^,]+', 1, level) as division from dual
                                   connect by level <= regexp_count(('CA','CO','IL','KS'), '[,]') + 1;

ERROR: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"

Can you help me figure out why this is erroring out.

EDIT - I cannot manipulate the string to have extra quotes in there. This is a fixed format i get from a table. How can i strip it to get a row format output?

Upvotes: 1

Views: 148

Answers (1)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65198

You have a quotation problem(quote the whole term ('CA','CO','IL','KS') after adding extra quotes per each single quote), try this rather :

 SELECT regexp_substr((replace(replace(replace('(''CA'',''CO'',''IL'',''KS'')',
                      chr(40)),
                      chr(41)), 
                      chr(39))), '[^,]+', 1, level) AS division 
   FROM dual
CONNECT BY level <= regexp_count('(''CA'',''CO'',''IL'',''KS'')', ',') + 1;

DIVISION
--------
CA
CO
IL
KS

Demo

Upvotes: 1

Related Questions