Re: Inserting an empty string inserts a space

I've seen various answers, but I think the info I've added is new to this discussion... >From: lantz <lantz@osm.com> >Date: Mon, 30 Jun 1997 13:45:18 -0700 >X-Informix-List-Id: <news.39883> > >I don't know the particulars about how the data is handled internally by >informix. Black box techniques reveal that informix doesn't seem to >distinguish between an empty string, a string with a single space >character, or strings with multiple space characters. Try to query from >your table where url = ''; I think it will find your row even though it >displays it as a single space. >======================================================================== >Tauren Mills wrote: >> UPDATE product_info SET prod_id = 1, prod_type_info_id = 1,>> short_description = 'Test', url = '', sort_order = 1 WHERE >> prod_info_id=1; >> >> The field "url" should be set to an empty string, but every time this >> statement is executed, the "url" field has a single space in it: ' '. The URL field was a VARCHAR field in the CREATE TABLE statement in the original question. Informix products treat the literal string '' (or, equivalently, "") as equivalent to ' ', a single space. For fixed CHAR fields (eg CHAR(40)), this is OK; there is no difference between one space and 20 spaces, and the comparisons will work correctly. Trailing blanks are not significant. With VARCHAR fields, trailing blanks are significant. Unfortunately, the string '' is still treated as a CHAR literal, rather than as a VARCHAR literal (well, that's one way of looking at it -- it isn't treated as an empty string, anyway). So, you cannot insert an empty, non-null VARCHAR string using a literal. However, with a programming language (eg I4GL or ESQL/C), you can indeed insert an empty string by using an empty host variable. For example (using archaic but compact ESQL/C notation): $varchar x[3]; x[0] = '\\0'; $UPDATE product_info SET url = $x WHERE ...; Note that NULL is not the same as '', and the database uses a different storage representation for the string '', NULL, and an empty VARCHAR variable. '' --> '\\001' '\\040' (2 bytes) NULL --> '\\001' '\\000' (2 bytes) empty --> '\\000' (1 bytes) Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: I decline to respond to messages with anti-spam in the return path.