snowflake regex_replace not working as expected

Question

I am trying to do this that strips "['" or "']" in the string. For Example, if we have ['Customer Name'] it should be "Customer Name"

select regexp_replace("['Customers NY']","\\['|\\']","") as customername;

I am getting this error-- SQL compilation error: error line 1 at position 22 invalid identifier "['Customers NY']"

Answer 1

It's a typo..

ALL string in SQL are single quotes only.. you double quotes are for named objects like columns/tables.

Then you will have to escape the quotes in the quotes

select regexp_replace('[\'Customers NY\']','\\[\'|\'\\]','') as customername;

gives:

CUSTOMERNAME
Customers NY

Answer 2

Double $$ makes escaping easier. Combine that with translate and you could do

select translate('[\'Customers NY\']',$$[']$$,'');