sql - 在双引号之间替换字符串中的字符

Question

我想在动态位置之间(即双引号之间)替换我的字符串中的逗号。请注意，如果重要的话，我的字符串中不会出现超过 2 次双引号。

我的例子:

'randomtext,123,"JEAN SEBASTIEN, GUY, DANIEL",sun'

期望的输出:

'randomtext,123,"JEAN SEBASTIEN GUY DANIEL",sun'

到目前为止，我已经尝试过将 REGEXP_REPLACE() 与 INSTR() 混合使用，但无法完成任何事情。

干杯

Answer 1

简短而干净。

with t(str) as (select 'randomtext,123,"JEAN SEBASTIEN, GUY, DANIEL",sun' from dual)
select regexp_replace(str,'(^[^"]*|[^"]*$)|,','\1') as result
from   t

-

+------------------------------------------------+
|                     RESULT                     |
+------------------------------------------------+
| randomtext,123,"JEAN SEBASTIEN GUY DANIEL",sun |
+------------------------------------------------+

SQL Fiddle

此外-
简短干净的通用版本

with t(str) as 
(
             select 'Well,you,went,uptown,riding,in,your,limousine' from dual
  union all  select 'With,your,fine,"Park, Avenue, clothes"' from dual
  union all  select 'You,had,the,"Dom, Perignon",in,your,hand,"And, the, spoon",up,your,nose' from dual
  union all  select '"And, when, you",wake,"up, in, the, morning"' from dual
  union all  select '"With, your, head, on, fire"' from dual
  union all  select '"And",your,"eyes, too, bloody","to, see",Go,"on, and, cry, in",your,coffee,"But","don''t","come, bitchin''","to, me"' from dual

)
select regexp_replace(str, '((^|").*?("|$))|,', '\1') as result
from   t 

--

+------------------------------------------------------------------------------------------------------------+
|                                                   RESULT                                                   |
+------------------------------------------------------------------------------------------------------------+
| Well,you,went,uptown,riding,in,your,limousine                                                              |
| With,your,fine,"Park Avenue clothes"                                                                       |
| You,had,the,"Dom Perignon",in,your,hand,"And the spoon",up,your,nose                                       |
| "And when you",wake,"up in the morning"                                                                    |
| "With your head on fire"                                                                                   |
| "And",your,"eyes too bloody","to see",Go,"on and cry in",your,coffee,"But","don't","come bitchin'","to me" |
+------------------------------------------------------------------------------------------------------------+

SQL Fiddle