isnull() function is not working for a NULL field
Moderators: chulett, rschirm, roy
isnull() function is not working for a NULL field
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.
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.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Re: isnull() function is not working for a NULL field
as ArndW said it doesn't contain null if you want to make it null or the value you wantrampstage 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.
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>
Search button is also available
try to search the solution before posting
Regards,
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.![Wink :wink:](./images/smilies/icon_wink.gif)
Genius may have its limitations, but stupidity is not thus handicapped.
![Wink :wink:](./images/smilies/icon_wink.gif)