replace blank values with null

Question

Post reply

replace blank values with null

sqlfriend

SSC Guru

Points: 52794
More actions
December 7, 2011 at 3:28 pm

#247343

I'm importing data from flat text file to sql db. There is some blank values in gender column in the txt file.
In the database the column name is gender with values 1 or 2 or null in database, it is a char(1) data type.
How can I make it to Null if it is blank.
Currently I'm using
(DT_STR,1,1252)SUBSTRING(Gender,1,1)
How can I add if it is blank then null in Derived column, also please help with data conversion too.
Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

A little rusty on my expressions..

And this was a lot harder than I expected it to be..

Tried:

SUBSTRING(Genrder,1,1) == " " ? NULL(DT_STR,1,1252) : SUBSTRING(Gender,1,1)

It didn't like DT_STR, SO, I tried:

SUBSTRING(Genrder,1,1) == " " ? NULL(DT_WSTR,1) : SUBSTRING(Gender,1,1)

And that worked, I went in with the advanced editor and changed the type to DT_STR with a length of 1, I think that will do it..

CEWII

sqlfriend SSC Guru Points: 52794 More actions · Answer 2

sqlfriend

SSC Guru

Points: 52794

December 8, 2011 at 12:28 pm

#1418912

Thank you, I will give it a try

sqlfriend SSC Guru Points: 52794 More actions · Answer 3

Elliott Whitlow (12/7/2011)
A little rusty on my expressions..
And this was a lot harder than I expected it to be..
Tried:
SUBSTRING(Genrder,1,1) == " " ? NULL(DT_STR,1,1252) : SUBSTRING(Gender,1,1)
It didn't like DT_STR, SO, I tried:
SUBSTRING(Genrder,1,1) == " " ? NULL(DT_WSTR,1) : SUBSTRING(Gender,1,1)
And that worked, I went in with the advanced editor and changed the type to DT_STR with a length of 1, I think that will do it..
CEWII

Thanks, what does NULL(DT_WSTR,1) mean? The null is one unicode length?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

Yes, unlike DT_STR where a codepage is specified that is why it only specifies length. We force it to DT_STR using the advanced editor.

CEWII

cbianchi Newbie Points: 4 More actions · Answer 5

If you're just looking to handle nulls, there's a third party data flow component called "Null Manager" from Tactek Data Systems (www.tactek.com). It's for SSIS 2008 only but it's cheap (like $10 bucks). It handles converting nulls to empty strings, empty strings to nulls, and nulls to alternate strings.

There's also PragmaticWorks' Task Factory that has a Null Handler tool. It's more expensive because you have to buy the whole Task Factory (it's about $800 bucks) but if you're looking to use a lot of the other features in it, I've played with that one, too. Overkill compared to the other option. It works for SSIS 2005 and 2008.

I just got tired of writing the code that goes along with all of the NULL(...) function junk over and over. These just provided a bit more "elegant" solutions.