Rameshs
New User
Joined: 15 Jun 2005
Posts: 53
Location: India, Chennai
|
|
|
|
Last_Name First_Name Company
--------------------------------------
AAAAAA BBBBBB COMP1
CCCCCC DDDDDD COMP2
GGGGGG HHHHHH COMP3
JJJJJJ KKKKKK ' ' (six blank spaces)
XXXXXX YYYYYY ' ' (six blank spaces)
LLLLLL MMMMM Null
NNNNNN OOOOOO Null
1. All the following query returns the same result. why ?
SELECT * FROM SQLTSR.TEST_TABLE WHERE COMPANY = ''; (no blank space)
SELECT * FROM SQLTSR.TEST_TABLE WHERE COMPANY = ' '; (single space
SELECT * FROM SQLTSR.TEST_TABLE WHERE COMPANY = ' '; (triple space)
Result:
JJJJJJ KKKKKK ' ' (blank)
XXXXXX YYYYYY ' '
Thanks,
Ramesh |
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
Hello Ramesh,
Before we talk about you specific questions, let?s look at decimal compares.
If we have two variables:
VAR1 PIC S9(9) VALUE 75.
VAR2 PIC S9(3) VALUE 75.
I don?t think there is any question that the value these two variables are the same. Right? Likewise, if you coded
IF VAR2 = 0000075
You know they will compare equal.
If is assumed that there can be any number of leading zeros without affecting the value in the variable. So, you can assume that any smaller variable will have leading zeros added up to the length of the largest variable.
Now, lets talk about the value of nothing. If you were told to put a value on nothing in VAR1, I suspect you would move zero (0) to VAR1, It?s assumed by most people to be a value of nothing. But, it is a value. It?s the integer value between -1 and +1, as the value of 5 is the integer value between 4 and 6. But DB2 recognizes that maybe you really want to say, ?this variable has nothing in it, its value is unassigned, there is nothing in this variable?. This is where the concept of a null value comes in. It says that the variable has not been assigned a value. Therefore, null and non-null nothing are not the same thing.
Now to you question. As in decimal non-null numbers where it is assumed that any number of leading zeros will not affect the value in the variable, char strings assume that any number of training spaces will not affect the value of the variable (in common character strings). We can assume that shorter strings can have spaces (blanks) added to the end of the variable without affecting the value of the string.
So. ?? no space, ? ? one space, and ? ? three spaces have the same string value, and are equivalent of each other. But ?? no spaces, in this context, is not the same as null, or an unassigned value.
Now do you understand you results?
If not, please come back and we can go into this further.
Dave |
|
