Check the following input/output examples:

1. Input: ""Nag"ndra"" -> Expected output: "Nag"ndra"
2. Input: 'N'agendra -> Expected output 'N'agendra

I tried the below query to implement that behavior, which is able to remove the starting and ending quotes.

select regexp_replace('""Nag"endra""','^["\']|["\']$','') from dual

for second example it is given as N'agendra it should be 'N'agendra

CodePudding user response：

If you want to keep the starting ' then don't use it in the regex. This works for me:

select regexp_replace('""Nag"endra""','^["]|["\']$',''); -- "Nag"endra"
select regexp_replace('\'N\'agendra','^["]|["\']$',''); -- 'N'agendra

Observe I removed the ' from ^["\'].

CodePudding user response：

If the regex flavour supports backreferences to a capturing group inside the expression, you could match the delimiter at the beginning of the string and reference it at the end, then replace by the contents of a second capturing group:

SELECT REGEXP_REPLACE( '""Nag"ndra""', '^(["\'])(.*)\\1$', '$2' );

If it does not, but supports backreferences in the replacement part, you could use two calls. For example:

SELECT REGEXP_REPLACE( '""Nag"ndra""', '^"(.*)"$', '$1' );
SELECT REGEXP_REPLACE( '\'N\'agendra', '^\'(.*)\'$', '$1' );

You'd probably want to test for the existence of a matched pair of quotes first, then select the one you need if you only want to remove the outer pair of quotes in this case.

Edit If neither is supported, you could use a conditional and your own expression. You have to make sure that your beginning quotes are matched at the end first:

SELECT IF(
    '""Nag"ndra""' REGEXP '^".*"$|^\'.*\'$',
    REGEXP_REPLACE( '""Nag"ndra""', '^["\']|["\']$', '' ), 
    '""Nag"ndra""'
);