behaviour of 'a single space' and 'null' in columns

Hi, I have seen some confusing behaviour with a table column defined as not null. One of our columns in a table (say mytable) is like this(simplified): Column name Type Nulls ------------ ------ ----- keyfield1 char(10) no field1 char(1) no We insert a single space when we insert a record into this table and if field1 has no specific value for it. But I can select such a row by both these queries: select * from mytable where keyfield=<some value> and field1 = ' ';and select * from mytable where keyfield=<some value> and field1 = ''; The first one checks for the single space ' ' and second one checks for the empty string. How are the two equivalent ? Note, that the following query does not return the row, which is correct since this column can't be null(and is not null). select * from mytable where keyfield=<some value> and field1 is null; Thanks. -AH

Atiq Hashmi wrote: > > Hi, > > I have seen some confusing behaviour with a table column defined > as not null. > One of our columns in a table (say mytable) is like this(simplified): > > Column name Type Nulls > ------------ ------ ----- > keyfield1 char(10) no > field1 char(1) no > > We insert a single space when we insert a record into this table > and if field1 has no specific value for it. > > But I can select such a row by both these queries: > select * from mytable where keyfield=<some value> and field1 = ' ';> and > select * from mytable where keyfield=<some value> and field1 = '';> > The first one checks for the single space ' ' and second one checks > for the empty string. How are the two equivalent ? ... what do you think will be stored inside your field1 if you'd enter an empty string ? Always keep in mind that we live in a world of "bytes" and a byte has a value. It's strange - a lot of people think there should be a difference between an empty string and a string containing a single space. Why don't you ask if there's also a difference between a string containing a single "space" and a string with two "spaces" ? If you define a FIXED LENGTH string ( CHAR(n) ), Informix fills the remaining bytes with spaces ( hex 20 ). i.e. create table t1(f1 char(5)); insert into t1 values("A"); ----> if you would dump the contents of field1 you would see the value 0x41 0x20 0x20 0x20 0x20 insert into t1 values( " " );----> if you would dump the contents of the field1 you would now see 0x20 0x20 0x20 0x20 0x20 And finally: insert into t1 values( "" ); ----> ... you would see 0x20 0x20 0x20 0x20 0x20 There's no difference between an empty string and a "single space" or "two spaces" - string. A NULL value will be stored different. insert into t1 values( NULL ); ----> ... you would see 0x00 " and 4 bytes garbage" hope I could make it clear now :-) Best regards Stefan Weideneder Phone: +49 89/3565478-2 --------------- --- Fax: +49 89/3565478-3 ------------- ------ mailto:/stefan@weideneder.de --- -------- http://www.weideneder.de -----