Replace blank source columns values to NULL before loading to target tables

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.

CodePudding user response：

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.

CodePudding user response：

The following query might work for you.

WHEN PATINDEX('%[0-9]%',TABLENAME)=0 AND PATINDEX('%[a-z]%',TABLENAME)=0 THEN NULL
 ELSE TABLENAME
END ````