How to handle numbers in column names in Snowflake
I am converting SQL Server tables to snowflake and some column names have numbers in them which are accepted in SQL Server but not snowflake. It's important that they retain the same name. Does anyone have any info on how to get around this syntax issue?
--- ms sql server syntax ---
CREATE TABLE naviguard.dbo.NPR_PROC_CD_MODS_IMPACT (
proc_cd varchar(7) COLLATE Latin1_General_CI_AS NOT NULL,
PROC_SRV_TYP_CD varchar(3) COLLATE Latin1_General_CI_AS NULL,
clm_cnt bigint NULL,
[0_mods_clm_cnt] bigint NULL, --- columns with numbers
[0_mods_clm_pct] decimal(5,4) NULL,
[1_mods_clm_cnt] bigint NULL,
[1_mods_clm_pct] decimal(5,4) NULL,
[2_mods_clm_cnt] bigint NULL,
[2_mods_clm_pct] decimal(5,4) NULL,
[3_mods_clm_cnt] bigint NULL,
[3_mods_clm_pct] decimal(5,4) NULL,
[4_mods_clm_cnt] bigint NULL,
[4_mods_clm_pct] decimal(5,4) NULL,
mods_var_mode1 varchar(12) COLLATE Latin1_General_CI_AS NULL,
mods_var_mode1_clm_cnt bigint NULL,
mods_var_mode2 varchar(12) COLLATE Latin1_General_CI_AS NULL,
mods_var_mode2_clm_cnt bigint NULL,
mods_var_mode3 varchar(12) COLLATE Latin1_General_CI_AS NULL,
mods_var_mode3_clm_cnt bigint NULL,
mods_var_3_mode_cnt bigint NULL,
mods_var_3_mode_pct decimal(5,4) NULL,
any_mods_clm_cnt bigint NULL,
any_mods_clm_pct decimal(5,4) NULL,
impact_mods_clm_cnt bigint NULL,
impact_mods_clm_pct decimal(5,4) NULL,
CONSTRAINT PK__NPR_PROC__A2159902E557C34D PRIMARY KEY (proc_cd)
);
--- snowflake syntax ---
CREATE TABLE naviguard.dbo.NPR_PROC_CD_MODS_IMPACT (
proc_cd varchar(7) NOT NULL,
PROC_SRV_TYP_CD varchar(3) ,
clm_cnt bigint,
0_mods_clm_cnt bigint, --- unaccepted number columns
0_mods_clm_pct decimal(5,4),
1_mods_clm_cnt bigint,
1_mods_clm_pct decimal(5,4),
2_mods_clm_cnt bigint,
2_mods_clm_pct decimal(5,4),
3_mods_clm_cnt bigint,
3_mods_clm_pct decimal(5,4),
4_mods_clm_cnt bigint,
4_mods_clm_pct decimal(5,4),
mods_var_mode1 varchar(12) ,
mods_var_mode1_clm_cnt bigint,
mods_var_mode2 varchar(12) ,
mods_var_mode2_clm_cnt bigint,
mods_var_mode3 varchar(12) ,
mods_var_mode3_clm_cnt bigint,
mods_var_3_mode_cnt bigint,
mods_var_3_mode_pct decimal(5,4),
any_mods_clm_cnt bigint,
any_mods_clm_pct decimal(5,4),
impact_mods_clm_cnt bigint,
impact_mods_clm_pct decimal(5,4)
);
Error Message:
SQL compilation error: syntax error line 5 at position 1 unexpected '0'.
Simplified original, with a syntax error:
CREATE TABLE naviguard.dbo.NPR_PROC_CD_MODS_IMPACT (
clm_cnt bigint,
0_mods_clm_cnt bigint, --- unaccepted number columns
0_mods_clm_pct decimal(5,4)
Fixed with quotes:
CREATE TABLE naviguard.dbo.NPR_PROC_CD_MODS_IMPACT (
clm_cnt bigint,
"0_mods_clm_cnt" bigint, --- unaccepted number columns
"0_mods_clm_pct" decimal(5,4)
)
Note that now you'll always need to add quotes and type in lower caps when referring to these columns:
select clm_cnt, "0_mods_clm_cnt"
from NPR_PROC_CD_MODS_IMPACT