Need to insert special character in string as insert script

Question

I wanted to insert records having special character in snowflake.

Having record in source table :

order/date=2022-02-18/hour=12/85b3e2d8-0195-4238-b246-7ed6564ac464.json

I need to extract hour value i.e 12

I am able to extract the value using : cast(replace(substr(METADATA$FILENAME,28,2),'/','') as number)

But I need to create the insert script , I had tried : 'cast(replace(substr(METADATA$FILENAME,28,2),'/,'') as number)'

But getting error : FAILED CODE: 0 STATE: 22018 MESSAGE: Numeric value '5/' is not recognized

Answer 1

I tested your string in select and insert command as below:

  select cast(replace(substr('order/date=2022-02-18/hour=12/85b3e2d8-0195-4238-b246-7ed6564ac464.json',28,2),'/','') as integer);

  create table t1(c1 number);

  insert into t1(c1) select cast(replace(substr('order/date=2022-02-18/hour=12/85b3e2d8-0195-4238-b246-7ed6564ac464.json',28,2),'/','') as integer);  

If your issue is different, then share the exact command that you are executing and that's failing.

Answer 2

I got the solution :

I wanted to insert this whole statement as string , I was facing issue due to special characters : / and '' . Used backslash to resolve it.