错误:ORA-00907:缺少右括号 - 你能帮忙找出问题吗 [英] 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;  

错误:ORA-00907: 缺少右括号00907. 00000 - 缺少右括号"

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?

推荐答案

你有一个引用问题(引用整个词条 ('CA','CO','IL','KS') 在为每个单引号添加额外的引号后)，试试这个:

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

演示

这篇关于错误:ORA-00907:缺少右括号 - 你能帮忙找出问题吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！