CODEWITHSUNDEEP
sql-serversql-server-2008ssis
prav
prav

Reputation: 450

blank to numeric conversion derived column

I have a source column with blank (not "NULL"), and target as numeric. while converting using the data conversion it is not converting due to balnk source value so I used derived column to replace a blank value with NULL or 0 as

(source column == " ") ? "0" : source column

but its not giving the value as 0 in the blank place.

thanks prav

Upvotes: 0

Views: 5660

Answers (3)

user2462054
user2462054

Reputation: 53

I'm pretty sure that the codingbadger answer is missing 1 =. I used that answer but to make it valid I had to write it as:

(LEN(TRIM(sourceColumn)) == 0 ? "0" : sourceColumn)

Upvotes: 0

Anthony Faull
Anthony Faull

Reputation: 17957

([source column] == null || [source column].TrimEnd().Length == 0)
     ? "0"
     : [source column]

Upvotes: 0

codingbadger
codingbadger

Reputation: 43984

This should do the trick.

(LEN(TRIM(sourceColumn)) = 0 ? "0" : sourceColumn)

Upvotes: 2

Related Questions