If a string is enclosed in quotes either ” or ‘. I have to remove those starting and ending quotes in snowflake

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

Answer

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 ^[“‘].