How to default Zero-length string to NULL ?

Question

Post reply

How to default Zero-length string to NULL ?

SQL Guy 1

SSCoach

Points: 15786
More actions
May 20, 2010 at 4:02 pm

#221280

We have daily batch processing system that loads about 7 mln records into a table with 130 columns. There are a lot of empty values, they are not null but just '' (zero-length strings).
We need to have something, default or anything except triggers, that would convert these '' into nulls when we load the data. Any ideas appreciated.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

tim.hansen SSC Veteran Points: 286 More actions · Answer 1

How about a simple update query add the end of the batch that updates the columns to NULL where value = ''?

Living in Paradise: N 34°16'07.99" W 119°12'17.58"

Ben Teraberry SSChampion Points: 11815 More actions · Answer 2

From a T-SQL standpoint you can do something like this:

update myTable

set Column1 = case Column1 when '' then null end,

Column2 = case Column2 when '' then null end,

Column3 = case Column3 when '' then null

where <some condition>

However, if possible, it would be better to transform '' to null in the course of the actual import, but the feasibility of doing that depends on the tool you are using for the import.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Take a look in Books Online at the NULLIF() function. It may not help directly for the load into your staging table (you do have a staging table, I hope) but it will easily convert such empty strings to nulls during the transfer to the final table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

SQL Guy 1 SSCoach Points: 15786 More actions · Answer 4

I know about an update query after the loading data, but this is my last resort if nothing else worked.

We don't have a staging table, the data is loaded from a text file using bcp/ bulk insert. I will also try to look into SSIS.

But probably the most elegant solution would be to do something while data is being bcp-ed. This is what I am trying to find.

Maddy...! SSC Eights! Points: 995 More actions · Answer 5

hello....

if u dont mind plz let me knw how u are loading the data through a wizard or through some scripts like vb script or bulk insert ... etc

so that we can find out a solution for it....

and the environment is it sql2005 or sql2008

SQL Guy 1 SSCoach Points: 15786 More actions · Answer 6

No, we are not using wizard. The data is loaded with bcp / bulk insert. Version 2005

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Have you looked into Jeff's suggestion yet?

NULLIF(MyDodgyColumn, '')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden