Replacing characters in a string when between double quotes

user_8642243 0
Viewed 158

I would like to replace commas in my strings, between dynamic positions (ie. between double quotes). Note that I will not have more than 2 occurrences of double quotes in my strings if that matters.

My example: 

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

Desired output:

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

So far I've tried things with REGEXP_REPLACE() mixed with INSTR() but could not get anything done.

Cheers

oracle sql
2 Answers

Short & clean.

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

In addition -
Short and clean generic version

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

user_63364790

Assuming you are working on CSV, then it is possible that you will also have nested double quotes as per this sample data:

CREATE TABLE test_data ( value ) AS
SELECT 'randomtext,123,"JEAN SEBASTIEN, GUY, DANIEL",sun' FROM DUAL UNION ALL
SELECT 'randomtext,123,"A, ""BC"", D",sun' FROM DUAL;

You can use the regular expression ^(.*?)("([^\"]|\\")+")(.*)$ to match the terms before, inside the quotes and after and then replace commas in only the middle parts:

SELECT value,
       REGEXP_SUBSTR( value, '^(.*?)("([^\"]|"")+")(.*)$', 1, 1, NULL, 1 )
       || REPLACE(
            REGEXP_SUBSTR( value, '^(.*?)("([^\"]|"")+")(.*)$', 1, 1, NULL, 2 ),
            ','
          )
       || REGEXP_SUBSTR( value, '^(.*?)("([^\"]|"")+")(.*)$', 1, 1, NULL, 4 ) replaced_value
FROM   test_data

Which outputs:

VALUE                                            | REPLACED_VALUE                                
:----------------------------------------------- | :---------------------------------------------
randomtext,123,"JEAN SEBASTIEN, GUY, DANIEL",sun | randomtext,123,"JEAN SEBASTIEN GUY DANIEL",sun
randomtext,123,"A, ""BC"", D",sun                | randomtext,123,"A ""BC"" D",sun

db<>fiddle here

Update

If you need to handle multiple quoted terms in a string (with nested quotes):

CREATE TABLE test_data ( value ) AS
SELECT 'randomtext,123,"JEAN SEBASTIEN, GUY, DANIEL",sun' FROM DUAL UNION ALL
SELECT 'randomtext,123,"A, ""BC"", D",sun' FROM DUAL UNION ALL
SELECT 'E,"F, G",H,"I, ""J""", K' FROM DUAL UNION ALL
SELECT 'L,M,N' FROM DUAL;

Then you can use a recursive sub-query factoring clause:

WITH replacements( value, prefix, suffix ) AS (
  SELECT value,
         REGEXP_SUBSTR( value, '^(.*?)("([^\"]|"")+"|$)(.*)$', 1, 1, NULL, 1 )
         || REPLACE(
              REGEXP_SUBSTR( value, '^(.*?)("([^\"]|"")+"|$)(.*)$', 1, 1, NULL, 2 ),
              ','
            ),
         REGEXP_SUBSTR( value, '^(.*?)("([^\"]|"")+"|$)(.*)$', 1, 1, NULL, 4 )
  FROM   test_data
UNION ALL
  SELECT value,
         prefix
         || REGEXP_SUBSTR( suffix, '^(.*?)("([^\"]|"")+"|$)(.*)$', 1, 1, NULL, 1 )
         || REPLACE(
              REGEXP_SUBSTR( suffix, '^(.*?)("([^\"]|"")+"|$)(.*)$', 1, 1, NULL, 2 ),
              ','
            ),
         REGEXP_SUBSTR( suffix, '^(.*?)("([^\"]|"")+"|$)(.*)$', 1, 1, NULL, 4 )
  FROM   replacements
  WHERE  suffix IS NOT NULL
         
)
SELECT value,
       prefix AS replaced_value
FROM   replacements
WHERE  suffix IS NULL;

Which outputs:

VALUE                                            | REPLACED_VALUE                                
:----------------------------------------------- | :---------------------------------------------
L,M,N                                            | L,M,N                                         
randomtext,123,"JEAN SEBASTIEN, GUY, DANIEL",sun | randomtext,123,"JEAN SEBASTIEN GUY DANIEL",sun
randomtext,123,"A, ""BC"", D",sun                | randomtext,123,"A ""BC"" D",sun               
E,"F, G",H,"I, ""J""", K                         | E,"F G",H,"I ""J""", K

db<>fiddle here

user_15092640
Related