On 10/28/2010 3:29 AM, crizoo4712 wrote:

> Hi all,
> what is the value stored in a varchar() column by:
> update tab1 set col1 = '' ?

That's easy. A varchar is stored as two bytes of length
whose value is the number of significant characters. So
in a column defined as varchar(6), an empty string is
represented as 0x0, 0x0, plus 6 bytes of whatever happens
to be in the buffer, normally zeros.

> It can be selected the same way (select * from tab1 where col1 = ''
>
> But..
>
> ..select cast(col1 as varchar(128) character set octets) from tab1
> ..doesn't show anything (even not<NULL> or hex low value)!?!

Right. The length is zero, so nothing is returned.

Just so people don't get confused, in memory and in messages,
varchar fields are stored at their full declared length. Before
the memory format of a record goes to disk, it suffers run length
compression, so - ignoring everything around it - the empty
varchar(6) is stored as two bytes with binary values of -8 and 0.


Cheers,

Ann