isnull() function is not working for a NULL field

[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.

[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.

[priyadarshikunal]

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,