SQL-Server replace empty cells with NULL value

Question

I am using SSIS to move excel data to a temp sql server table and from there to the target table. So my temp table consists of only varchar columns - my target table expects money values for some columns. In my temp table the original excel columns have a formula but leave an empty cell on some rows which is represented by the temp table with an empty cell as well. But when I cast one of these columns to money these originally blank cells become 0,00 in the target column.

Of course that is not what I want, so how can I get NULL values in there? Keeping in mind that it is possible that a wanted 0,00 shows up in one of these columns.

I guess I would need to edit my temp table to turn the empty cells to NULL. Can I do this from within a SSIS package or is there a setting for the table I could use?

thank you.

Fedor Hajdu · Accepted Answer · 2012-09-07 07:49:46Z

9

For existing data you can write a simple script that updates data to NULL where empty.

UPDATE YourTable SET Column = NULL WHERE Column = ''

For inserts you can use NULLIF function to insert nulls if empty

INSERT INTO YourTable (yourColumn)
SELECT NULLIF(sourceColum, '') FROM SourceTable

Edit: for multiple column updates you need to combine the two solutions and write something like:

UPDATE YourTable SET 
Column1 = NULLIF(Column1, '')
, Column2 = NULLIF(Column2, '') 
WHERE Column1 = '' OR Column2 = ''

etc

That will update all

edited Sep 7, 2012 at 7:49

answered Sep 7, 2012 at 7:11

Fedor Hajdu

4,7053 gold badges35 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

TonyC Over a year ago

thank you Fedor this does it for me. One added question: What is the syntax for two or more columns for the UPDATE statement?

Fedor Hajdu Over a year ago

UPDATE YourTable SET Colum1 = value, Column2 = value2 etc

TonyC Over a year ago

UPDATE YourTable SET Column1 = NULL WHERE Column1 = '', Column2 = NULL WHERE Column2 = '' says wrong syntax at ','

Fedor Hajdu Over a year ago

No, just one WHERE at the end. I'll update my answer so we have syntax coloring...

Collectives™ on Stack Overflow

SQL-Server replace empty cells with NULL value

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related