RE: Air pocket char fields.
- From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxx>
- To: "Joel.Patterson@xxxxxxxxxxx" <Joel.Patterson@xxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 8 Dec 2011 07:36:12 -0500
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
