Snowflake Tips And Tricks: How to select/insert Strings that Include Quotes or Special Characters?

In this video, I am going to show you How to select/insert Strings that Include Quotes or Special Characters in snowflake?
-- String : All the President's men
--SELECT 'All the President's men' as string;
SELECT 'All the President''s men' as string ;
SELECT 'All the President''''s men' as string ;
SELECT $$All the President's men$$ as string ;
SELECT $$All the President''s men$$ as string ;
--SELECT '\' ;
SELECT $$\$$ ;
SELECT $$\\$$ ;
create or replace table Test_Quotes( id number,col1 string);
--insert into test_Quotes values ( 1, 'All the President's men');
insert into test_Quotes values ( 1, 'All the President''s men');
select * from test_Quotes;
insert into test_Quotes values ( 2, $$All the President's men$$);
select * from test_Quotes;
insert into test_Quotes values ( 3, '$$');
select * from test_Quotes;
set str = $$All the President's men$$;
select $str ;
set str_backslash = $$\$$;
select $str_backslash;
insert into test_Quotes values ( 4, $str);
select * from test_Quotes;
--insert into test_Quotes values ( 4, '\');
insert into test_Quotes values ( 5, $$\$$);
select * from test_Quotes;
insert into test_Quotes values ( 5, $str_backslash);
select * from test_Quotes;
#snowflake#datacloud#vcklytech

[@marychieng2553]

Thanks. How do I replace [ ] in snowflake

[@bhavanig2582]

Thanks, it helped me while inserting a json to a variant type.

[@rojach4938]

Again nice one.can we use the same $$ while selecting the column from a file while loading into snowflake table

[@VCKLYTech]

we case use $$ while selecting the column from a file but that column would become as string.
Please check this.
create or replace file format mycsvformat
type = 'CSV'
field_delimiter = '|'
skip_header = 1;

create or replace stage my_csv_stage
file_format = mycsvformat
url = 's3://snowflake-docs';

select * from ( select $$abc'xyz$$ ,$1, $2 from @my_csv_stage/tutorials/dataloading/contacts1.csv);
Please note that no column names available while selecting data from file.
select id from @my_csv_stage/tutorials/dataloading/contacts1.csv; --SQL compilation error: error line 1 at position 7 invalid identifier 'ID'
So we have to use $1, $2 etc.
while selecting data from file.