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"
November 12, 2015 - 2:02 am UTC
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