Page 1 of 1
isnull() function is not working for a NULL field
Posted: Thu Feb 14, 2008 6:07 am
by rampstage
My source is fixed width binary file(.txt). For few columns I am getting Blank space from source which is not getting trimmed nor isnull() function is not working on that field. What function i can use to identify this value as i need to populate a default value when there is a space.
Posted: Thu Feb 14, 2008 6:13 am
by ArndW
A text file won't contain NULL values and you cannot trim a CHAR column. Either set a column default value or check for empty using a derivation such as 'IF Trim(In.Column)=""' which will create a temporary VarChar as the result of the trim() function. Or check for the correct number of spaces, i.e. check for 10 spaces on a char(10) column.
Re: isnull() function is not working for a NULL field
Posted: Thu Feb 14, 2008 6:24 am
by priyadarshikunal
rampstage wrote:My source is fixed width binary file(.txt). For few columns I am getting Blank space from source which is not getting trimmed nor isnull() function is not working on that field. What function i can use to identify this value as i need to populate a default value when there is a space.
as ArndW said it doesn't contain null if you want to make it null or the value you want
find its ascii value and then compare it with the ascii value you got
ascii value may be 0 or 32
so compare it like
Code: Select all
if seq(column)=32 then SetNull() else <remaining expression>
if your problem is not resolved try to find the ascii value of that character then use it your code
Search button is also available
try to search the solution before posting
Regards,