RE: Air pocket char fields.

Joel,

I hate to ask the obvious question.

Why are you counting the number of blank spaces?  
Seems like a questionable design to me.

If you want a count where the column contains all spaces, why not just:

Select count(*) from table where rtrim(column,' ') is null

Just asking.

Tom


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Joel.Patterson@xxxxxxxxxxx
Sent: Wednesday, December 07, 2011 3:51 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: 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




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

Other related posts: