RE: Air pocket char fields.
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <Thomas.Mercadante@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 8 Dec 2011 08:41:41 -0500
I am not counting the spaces. I am working on a query--which is not part of
the question.
This is just a question on behavior. Why is oracle returning what it is
returning when considering the below query examples, and only the below query
examples. There is no higher purpose.
Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----
From: Mercadante, Thomas F (LABOR) [mailto:Thomas.Mercadante@xxxxxxxxxxxx]
Sent: Thursday, December 08, 2011 7:36 AM
To: Patterson, Joel; oracle-l@xxxxxxxxxxxxx
Subject: 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
