Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question, Sandeep.

Asked: November 10, 2015 - 9:57 pm UTC

Last updated: November 12, 2015 - 2:02 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hello,

I have a string "ABCDEFDDDD ""SSFD"""(including space), I want the output as ABCDEFDDDD "SSFD" i.e replacing first and last double quote with nothing and replacing 2 consecutive double quotes with single double quote. Please note consecutiv double quotes can come anywhere in the string(position is not fixed).I am looking into the rexexp_substr but not getting.
I am trying with replace in combination with substr and instr but not getting :(

Please help.

Best Regards,
Sandeep

and Connor said...

It doesn't necessarily have to be regex, especially for the start and end characeters, but here's an example for you:

with t as ( 
  select '"ABCDEFDDDD ""SSFD"""' str from dual )
select 
  regexp_replace(
    regexp_replace(
      regexp_replace(str,'^"',''),         -- replace " at start with nothing
    '"$',''),                              -- replace " at end with nothing
  '""+','"')                               -- 2 or more " with a single
from t;


Hope this helps

Rating

  (7 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Vladislav, November 11, 2015 - 5:24 am UTC

In my opinion it is easier:
select replace(regexp_replace('"ABCDEFDDDD ""SSFD"""','"(.*)"','\1'),'""','''''') str from dual;

Should be possible with one regular expression

cd, November 11, 2015 - 6:38 am UTC

The requirements as I understood are

- Replace the first and last double quote (")
- Replace 2 consecutive double quotes ("") with one double quote (")

select regexp_replace('"ABCDEFDDDD ""SSFD"""', '^"|"$|"(")', '\1') str from dual


Result:
STR             
-----------------
ABCDEFDDDD "SSFD"

How about no regexp at all?

Tim, November 11, 2015 - 11:50 am UTC 

SELECT REPLACE(REPLACE(REPLACE(REPLACE( 
   reverse( 
      SUBSTR( 
         reverse( 
            SUBSTR('"ABCDEFDDDD """"""""""""""""SSFD"""', 2) --get pos 2 to end
                 )                                           --reverse it
       , 2)                                                  --get pos 2 to the end again
       )                                                     --reverse it back
, '"""""','"'), '""""', '"'), '"""', '"'), '""', '"')        --get rid of the duplicate quotes
FROM dual;

result : ABCDEFDDDD "SSFD"


Connor McDonald
November 12, 2015 - 2:02 am UTC

https://en.wiktionary.org/wiki/there%27s_more_than_one_way_to_skin_a_cat

Thanks

Sandeep, November 11, 2015 - 10:44 pm UTC

Thank you Very much, its a good learning. However, i took an easier approach, read it from 2nd position to 2nd last and replace consecutive double quotes with single in between
select replace( substr('"ABCDEFDDDD ""SSFD"""',2,length('"ABCDEFDDDD ""SSFD"""')-2),'""','"')from dual;

Since we're skinning cats

Stew Ashton, November 12, 2015 - 12:27 pm UTC

Here's a fairly simple non-REGEXP alternative (because REGEXP is CPU intensive):
with data as (
    select '"ABCDEFDDDD ""SSFD"""' txt from dual
)
select replace(trim('"' from txt), '""', '"') from data

Please disregard my previous review

Stew Ashton, November 12, 2015 - 2:38 pm UTC

My suggestion above removes all leading and trailing quotes, not just the first and last. Sorry about that.

It's irregular to use Regular Expressions on this cat

Duke Ganote, November 12, 2015 - 2:48 pm UTC

I've combined Tim from Plymouth UK's use of the undocumented REVERSE function with Reeds Lau from HK's elegant triple-REPLACE. I also generalized the parameters:

WITH
parms AS (
SELECT '"' as dlm -- delimiter to be de-duplicated
     , '@' as tmp -- temporary substitution
  FROM DUAL )
,
instring AS (
SELECT 'ABCDEFDDDD """"""""""""""""SSFD""' as qsv
 FROM dual )
,
trimmed_ends AS (
SELECT reverse(
       SUBSTR(
       reverse(
       SUBSTR(qsv, 2) --get pos 2 to end
                 )    --reverse it
       , 2)           --get pos 2 to the end again
       )              --reverse it back
       as qsv
  FROM instring
)
SELECT REPLACE(
       REPLACE(
       REPLACE( qsv ,dlm      ,dlm||tmp)
                    ,tmp||dlm ,''      )
                    ,dlm||tmp ,dlm     ) as
  FROM trimmed_ends
  CROSS JOIN parms;


REPLACE(REPLACE(
----------------
BCDEFDDDD "SSFD"


Reeds Lau's usage can be seen at this related thread:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:13912710295209#13998996415614
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.