1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle SQL Operators and Datatypes Allowed/Permitted

Discussion in 'SQL PL/SQL' started by ygsunilkumar, May 4, 2009.

  1. ygsunilkumar

    ygsunilkumar Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Hi.Please provide the Datatypes allowed for the each below mentioned operators, if possible for all the SQL Operators. Thanks:)

    =
    <>
    >
    >=
    <
    <=
    BETWEEN
    IN
    LIKE
    IS NULL
    AND
    OR
    NOT

    Please find the attached JPG file wherein my requirement is mentioned.
     

    Attached Files:

  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    hi

    that's a very abstract question, you know.

    Most commonly =, <>, give boolean results so the datatypes on either side can be char, varchar or number

    for >, >=, <= you would have number type datatypes and

    BETWEEN, LIKE would be character type datatypes and

    IN, IS NULL, AND,OR ,NOT aren't exactly comparison operator the way you understand them.

    Please go through this link as it explains everything in detail with examples

    http://download.oracle.com/docs/html/A95915_01/sqopr.htm
     
  3. ygsunilkumar

    ygsunilkumar Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Thanks for the reply Tyro:) the link provided will explain only the Operators but not the datatypes allowed for each of the operators. It will be very helpful if you can provide me the data which I have mentioned the attachment. Thanks:)

    And also you have mentioned that BETWEEN, LIKE would be character type datatypes but also it will allow the Date and Number datatypes right? Please confirm.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    LIKE is used for comparing the string/character only and not the Date or Number ( Comparing Number by using LIKE converts it implicitly to character)

    Code (SQL):

    SQL> SELECT empno, deptno FROM emp;

         EMPNO     DEPTNO
    ---------- ----------
          7369         20
          7499         30
          7521         30
          7566         20
          7654         30
          7698         30
          7782         10
          7788         20
          7839         10
          7844         30
          7876         20
          7900         30
          7902         20
          7934         10

    14 ROWS selected.

    SQL> SELECT empno, deptno FROM emp WHERE deptno LIKE 2;

    no ROWS selected

    SQL> SELECT empno, deptno FROM emp WHERE deptno LIKE 20;

         EMPNO     DEPTNO
    ---------- ----------
          7369         20
          7566         20
          7788         20
          7876         20
          7902         20

    SQL> SELECT empno, deptno FROM emp WHERE deptno LIKE 2%
      2  ;
    SELECT empno, deptno FROM emp WHERE deptno LIKE 2%
                                                     *
    ERROR at line 1:
    ORA-00911: invalid CHARACTER
     
    While Between can be used against Character/Date/Number datatypes.
     
  5. ygsunilkumar

    ygsunilkumar Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Thanks rajavu:) Can u please provide me the datatypes allowed or permitted for the rest of the Operators?
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Even I feel it is an Abstact question .

    The Operators you listed except LIKE can be used against Character,Number and Date Datatypes.

    Moreover its by exprience you to come to know about the difference in using these operators.