FW: Air pocket char fields.
Date: Wed, 7 Dec 2011 15:51:17 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1B8BF185A6_at_JAXMSG01.crowley.com>
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-lReceived on Wed Dec 07 2011 - 14:51:17 CST