0

I have source tables whose all columns are varchar and some of them have empty spaces like '' (not NULL). I am trying to load this table data into target table whose datatype are not varchar and hence while inserting the records having '' ,getting error as "Numeric values '' not found".

Could you please suggest how can I find and convert all columns which have '' to NULL before I load into target.

I am looking for this process to be dynamic as I have several other tables on which I have to perform similar activity.

I have tried following this URL for snowflake, but not able to achieve. How to convert empty spaces into null values, using SQL Server?

Appreciate your help.

Thank you.

user20119813
  • 1
  • _"but not able to achieve"_ - **why not?** T-SQL and Snowflake's DB are mutually-intelligible so what error did you get? – Dai Sep 30 '22 at 12:55

2 Answers2

1

In the Snowflake file format, you can add a parameter to specify that blank strings are treated as nulls.

NULL_IF = ('')

In a complete file format definition it would look something like this:

CREATE FILE FORMAT "MVP"."PUBLIC".MY_FILE_FORMAT TYPE = 'CSV'
COMPRESSION = 'AUTO' FIELD_DELIMITER = ',' RECORD_DELIMITER = '\n' 
SKIP_HEADER = 0 FIELD_OPTIONALLY_ENCLOSED_BY = 'NONE' 
TRIM_SPACE = FALSE
ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE ESCAPE = 'NONE' 
ESCAPE_UNENCLOSED_FIELD = '\134' DATE_FORMAT = 'AUTO' 
TIMESTAMP_FORMAT = 'AUTO' NULL_IF = ('');

If you use the UI to build a file format, specify Other for the Null string option and either leave it completely blank or specify two single quotes and nothing else in the text box.

Greg Pavlik
  • 10,089
  • 2
  • 12
  • 29
0

The following query might work for you.

WHEN PATINDEX('%[0-9]%',TABLENAME)=0 AND PATINDEX('%[a-z]%',TABLENAME)=0 THEN NULL
 ELSE TABLENAME
END
Astrinos
  • 24
  • 2
  • Please note that there is no PATINDEX function in Snowflake, you'll have to change to a REGEXP_INSTR function, and use POSIX regular expressions. – Jim Demitriou Sep 30 '22 at 23:20