Skip to Main Content
  • Questions

  • where variable!='value' on a varchar2 column is acting weird

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Laxmi vineela.

Asked: April 27, 2017 - 8:06 am UTC

Last updated: March 23, 2022 - 2:26 am UTC

Version: 11.2.0.3.0

Viewed 1000+ times

You Asked

Could you please explain me the reasons for the different outputs I am getting on dba_tables.

SQL> desc dba_tables
Name Null? Type
----------------------------------------- -------- ----------------------------
DEGREE VARCHAR2(40)

SQL> select distinct degree from dba_tables where degree!=1;
select distinct degree from dba_tables where degree!=1
*
ERROR at line 1:
ORA-01722: invalid number


SQL> select distinct degree from dba_tables where degree!='1';

DEGREE
----------------------------------------
1 -----displayed output but,it displayed 1 also-----
3
DEFAULT
4
2

SQL> select distinct degree from dba_tables where degree!='12';

DEGREE
----------------------------------------
1
3
DEFAULT
4
2

Here it has not displayed 12.

SQL> select * from dba_tables where degree=1;
select * from dba_tables where degree=1
*
ERROR at line 1:
ORA-01722: invalid number


SQL> select * from dba_tables where degree='1';

no rows selected

Please let us know how we should use a varchar2 datatype for the data retrieval.

and Connor said...

SQL> desc dba_tables
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                 NOT NULL VARCHAR2(128)
 TABLE_NAME                                            NOT NULL VARCHAR2(128)
 TABLESPACE_NAME                                                VARCHAR2(30)
 CLUSTER_NAME                                                   VARCHAR2(128)
 IOT_NAME                                                       VARCHAR2(128)
 STATUS                                                         VARCHAR2(8)
 PCT_FREE                                                       NUMBER
 PCT_USED                                                       NUMBER
 INI_TRANS                                                      NUMBER
 MAX_TRANS                                                      NUMBER
 INITIAL_EXTENT                                                 NUMBER
 NEXT_EXTENT                                                    NUMBER
 MIN_EXTENTS                                                    NUMBER
 MAX_EXTENTS                                                    NUMBER
 PCT_INCREASE                                                   NUMBER
 FREELISTS                                                      NUMBER
 FREELIST_GROUPS                                                NUMBER
 LOGGING                                                        VARCHAR2(3)
 BACKED_UP                                                      VARCHAR2(1)
 NUM_ROWS                                                       NUMBER
 BLOCKS                                                         NUMBER
 EMPTY_BLOCKS                                                   NUMBER
 AVG_SPACE                                                      NUMBER
 CHAIN_CNT                                                      NUMBER
 AVG_ROW_LEN                                                    NUMBER
 AVG_SPACE_FREELIST_BLOCKS                                      NUMBER
 NUM_FREELIST_BLOCKS                                            NUMBER
 DEGREE                                                         VARCHAR2(10)
...


You can see that "DEGREE" is a varchar2 column.

So when you do:

where DEGREE = 1

we have a mismatch in datatypes, so internally we do:

where to_number(degree) = 1

to bring them into alignment. Of course if we find a value for DEGREE that *cannot* be converted, eg, the word "DEFAULT" then we'll bomb out.

We just need to handle any leading spaces, 

SQL> select count(*) from dba_tables where degree = '1';

  COUNT(*)
----------
         0

SQL> select count(*) from dba_tables where ltrim(degree) = '1';

  COUNT(*)
----------
      2735

SQL> select count(*) from dba_tables where degree = lpad('1',10);

  COUNT(*)
----------
      2735

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 15, 2021 - 6:15 am UTC


How do I find out the tables which has a DEGREE greater than 1.

Arun, March 21, 2022 - 10:00 am UTC

How do I find out the tables which has a DEGREE greater than 1.

SQL> select owner, table_name, degree from dba_tables where degree > 1;

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.
Connor McDonald
March 23, 2022 - 2:26 am UTC

DEGREE is a string, because we need to store "DEFAULT"

So you can do

where trim(degree) not in ('DEFAULT','1')
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.