FW: Air pocket char fields.

I sent this to a couple DBAs, which warranted it to be posted for comment.

Do you know why this is? Shouldn't a CHAR field size 15 return all the records where the field is 15 spaces when requested? Should it not return all rows if <> anything but 15 spaces?

Oracle 11.2.0.1
Field is CHAR(15) NOT NULL
Min(length(activity)), max(length(activity)) = 15

714196 + 3744244 = total rows in table.

select count(*) SIXTEEN from lawcrp.mmdist where activity = ' ';

   SIXTEEN

   3744244

select count(*) FIFTEEN from lawcrp.mmdist where activity = ' ';

   FIFTEEN

   3744244

select count(*) FOURTEEN from lawcrp.mmdist where activity = ' ';

   FOURTEEN
---------- THIRTEEN, TWELVE, ELEVEN, ... THREE, TWO,
   3744244

select count(*) ONE from lawcrp.mmdist where activity = ' ';

       ONE

   3744244

select count(*) ZERO from lawcrp.mmdist where activity = '';

      ZERO

         0

select count(*) "<> ZERO" from lawcrp.mmdist where activity <> '';

   <> ZERO

         0

select count(*) "<> ONE" from lawcrp.mmdist where activity <> ' ';

    <> ONE

    714196

select count(*) "<> TWO" from lawcrp.mmdist where activity <> ' ';

    <> TWO
---------- THREE, FOUR, FIVE, SIX, ... THIRTEEN, FOURTEEN,
    714196

select count(*) "<> FIFTEEN" from lawcrp.mmdist where activity <> ' ';

<> FIFTEEN

    714196

select count(*) "<> SIXTEEN" from lawcrp.mmdist where activity <> ' ';

<> SIXTEEN

    714196

Joel Patterson
Database Administrator
904 727-2546

--
http://www.freelists.org/webpage/oracle-l