Hi
I have a developed job which checks for space and also ??(which i cant interpret)
if stringisspace(input.column) or inp.col="" then trim(lkp.column) else " "
does that above statement mean its checking for NULL?
If thats NULL, whats an empty string? does empty string has an ASCII value. I didnt find one in ascii table.
If its checking for multiple spaces or a single space, we cant trim a NULL..can we?
Thanks
empty string - null; same in oracle?
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Empty String
An empty string (also called a zero-length string, and sometimes erroneously called NULL), is a string that has no characters in it. Therefore it does not have an ASCII value (nor a Unicode value).
A zero-length string can participate happily in string comparisons (it is less than every other string). Within DataStage, in a numeric context, a zero-length string can be treated as 0, and in a Boolean context will be treated as FALSE.
NULL
NULL is represented differently in every database. It is a special representation of "unknown value".
Within DataStage, NULL is ordinarily represented as ASCII code 128, but provision is made in the uvconfig file to shift this to another code point particularly on Windows servers, where Microsoft decided to use 128 to represent the Euro character (after DataStage was already using 128 to represent NULL). For example, you could represent NULL internally in DataStage as ASCII code point 164.
NULL can not participate in comparisons. It can not participate in string or arithmetic operations. If such an attempt is made, the result is NULL. This is reasonable: "unknown" + 3 = "unknown" is the only sensible outcome. If the test expression in a conditional statement (such as IF) is NULL, then the ELSE clause is taken, on the grounds that one can not assert that the condition is satisfied, which is what triggers execution of the THEN clause.
NULL is represented in DataStage server expressions by the system variable @NULL. It can only be used as a value; not in a comparison (as noted earlier). The curious system variable @NULL.STR is "the string representation of the internal NULL character", and should not be used.
Len(@NULL) returns 0, for reasons I have not been able to fathom. However, this does not mean that a zero-length string and NULL are the same; they are not.
Checking for NULL
Unless there's something hidden in the stringisspace() function - which is a user-written function at your site - the expression you posted is not checking for NULL.
The only way to check for NULL in DataStage is to use the IsNull() function, or the IsNulls() function for multi-valued data.
An empty string (also called a zero-length string, and sometimes erroneously called NULL), is a string that has no characters in it. Therefore it does not have an ASCII value (nor a Unicode value).
A zero-length string can participate happily in string comparisons (it is less than every other string). Within DataStage, in a numeric context, a zero-length string can be treated as 0, and in a Boolean context will be treated as FALSE.
NULL
NULL is represented differently in every database. It is a special representation of "unknown value".
Within DataStage, NULL is ordinarily represented as ASCII code 128, but provision is made in the uvconfig file to shift this to another code point particularly on Windows servers, where Microsoft decided to use 128 to represent the Euro character (after DataStage was already using 128 to represent NULL). For example, you could represent NULL internally in DataStage as ASCII code point 164.
NULL can not participate in comparisons. It can not participate in string or arithmetic operations. If such an attempt is made, the result is NULL. This is reasonable: "unknown" + 3 = "unknown" is the only sensible outcome. If the test expression in a conditional statement (such as IF) is NULL, then the ELSE clause is taken, on the grounds that one can not assert that the condition is satisfied, which is what triggers execution of the THEN clause.
NULL is represented in DataStage server expressions by the system variable @NULL. It can only be used as a value; not in a comparison (as noted earlier). The curious system variable @NULL.STR is "the string representation of the internal NULL character", and should not be used.
Len(@NULL) returns 0, for reasons I have not been able to fathom. However, this does not mean that a zero-length string and NULL are the same; they are not.
Checking for NULL
Unless there's something hidden in the stringisspace() function - which is a user-written function at your site - the expression you posted is not checking for NULL.
The only way to check for NULL in DataStage is to use the IsNull() function, or the IsNulls() function for multi-valued data.
Last edited by ray.wurlod on Wed Jan 19, 2005 2:56 pm, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
peternolan9
- Participant
- Posts: 214
- Joined: Mon Feb 23, 2004 2:10 am
- Location: Dublin, Ireland
- Contact:
Re: empty string - null; same in oracle?
Brian,bryan wrote:Hi
I have a developed job which checks for space and also ??(which i cant interpret)
if stringisspace(input.column) or inp.col="" then trim(lkp.column) else " "
does that above statement mean its checking for NULL?
If thats NULL, whats an empty string? does empty string has an ASCII value. I didnt find one in ascii table.
If its checking for multiple spaces or a single space, we cant trim a NULL..can we?
Thanks
if you are using Oracle you have a problem. Oracle uses VARCHAR2 which does not conform to the ansi standard of VARCHAR.
An ansi standard VARCHAR field can be a zero length character string tested by strcmp(field,"") and it can be NULL. And they are different. To test for a NULL you must use a null indicator and in DS I believe that is @NULL. Unfortuantely, when a field is NULL the string that is returned in the field is often a zero length character string.
Oracle, in their infinite wisdom, for the implementation of VARCHAR2 will turn a zero length character string into a NULL when inserted/updated via ODBC. Funnily enough, not if it is loaded through SQL Loader I believe. And I know not if updated with PRO/Cobol on version 7 of oracle...but that's ancient history!!!!
I think it's sufficient to say you need to be careful of zero length character strings in Oracle. You need to see how they are being treated. I gave up some time ago and just allowed nulls in character strings when using Oracle.