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']"

this is almost a duplicate of your own question: stackoverflow.com/questions/71403989/… — Simeon Pilgrim
– Simeon Pilgrim, Commented Mar 25, 2022 at 12:42

Simeon Pilgrim · Accepted Answer · 2022-03-25 12:40:05Z

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

answered Mar 25, 2022 at 12:40

Simeon Pilgrim

26.7k3 gold badges38 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Divyacharan JVS Over a year ago

When I use it in the stored procedure of language "JavaScript". It is giving me the error Execution error in store procedure SAMPLE: Unsupported feature '|'. At Statement.execute, line 22 position 31

Simeon Pilgrim Over a year ago

Given this question was "how do I make this SQL work" this question has been answered, As to "how do I make valid SQL still valid when I embed it in a JavaScript stored procedure" that is another question. And I have given some guidance of that question also.

Rajat · Accepted Answer · 2022-03-25 17:27:54Z

1

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

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

answered Mar 25, 2022 at 17:27

Rajat

5,8733 gold badges14 silver badges30 bronze badges

Collectives™ on Stack Overflow

snowflake regex_replace not working as expected

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related