how to use BETWEEN for varchar field
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: how to use BETWEEN for varchar field

  1. #1
    Join Date
    Mar 2008
    Posts
    12

    how to use BETWEEN for varchar field

    hi ,
    I have table with receipt_no as varchar2 filed.
    when i use BETWEEN query for this field as condition it is giving me out of range values also.
    e.g when i use query :
    select * from test_detail
    where receipt_no between '510851' and '510900';
    it gives me 51086
    51087
    51088
    51089
    51090
    values also which i don't require.It should return only numbers between 510851 and 510900.only 50 numbers.
    I used to_number for varchar field receipt_no in below query but its giving me invalid number error:

    SELECT * FROM test_detail WHERE receipt_no BETWEEN TO_NUMBER('510851') AND TO_NUMBER('510900');

    Pls suggest how to use BETWEEN for varchar field.

    Thanks


    Table structure:
    CREATE TABLE TEST_DETAIL
    (
    RECEIPT_NO VARCHAR2(50 BYTE) NOT NULL,
    DATED DATE DEFAULT sysdate,
    STATUS NUMBER DEFAULT NULL
    );

  2. #2
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Before the answer, I will do just one comment : to use inproper datatypes (as a character datatype for keeping NUMBER values, like your case) is the ROOT of all evil in databases , it can (will, many times) bring more difficult to write queries, performance issues, no good at all....
    Your answer is simple, DONīT DO implicit conversions anymore, compare a NUMBER with NUMBERs, if your BETWEEN clause return a NUMBER, please compare it with A NUMBER, like :

    JLCHIAPPA@dwpan:SQL>CREATE TABLE TEST_DETAIL
    2 (
    3 RECEIPT_NO VARCHAR2(50 BYTE) NOT NULL,
    4 DATED DATE DEFAULT sysdate,
    5 STATUS NUMBER DEFAULT NULL
    6 );


    JLCHIAPPA@bdtest:SQL>insert into TEST_DETAIL (RECEIPT_NO) values(510851);
    JLCHIAPPA@bdtest:SQL>insert into TEST_DETAIL (RECEIPT_NO) values(510852);
    JLCHIAPPA@bdtest:SQL>insert into TEST_DETAIL (RECEIPT_NO) values(510853);
    JLCHIAPPA@bdtest:SQL>insert into TEST_DETAIL (RECEIPT_NO) values(51088);
    JLCHIAPPA@bdtest:SQL>insert into TEST_DETAIL (RECEIPT_NO) values(51089);
    JLCHIAPPA@bdtest:SQL>insert into TEST_DETAIL (RECEIPT_NO) values(51090);
    JLCHIAPPA@bdtest:SQL>commit;

    => your (WRONG!) way, comparing the string column with a NUMBER returned by the BETWEEN :

    Code:
    JLCHIAPPA@bdtest:SQL>select * from test_detail
       where receipt_no between '510851' and '510900';
     
    RECEIPT_NO                                         DATED                           STATUS
     -------------------------------------------------- ------------------- ------------------
     510851                                             28/07/2008 07:59:48
     510852                                             28/07/2008 07:59:55
     510853                                             28/07/2008 08:00:01
     51088                                              28/07/2008 08:00:17
     51089                                              28/07/2008 08:00:26
     51090                                              28/07/2008 08:00:36
    ==> the more correct way, NUMBER against NUMBER, no chances here to implicit conversions :


    Code:
    JLCHIAPPA@bdtest:SQL>select * from test_detail
     where to_number(receipt_no) between to_number('510851') and to_number('510900');
     
     RECEIPT_NO                                         DATED                           STATUS
     -------------------------------------------------- ------------------- ------------------
     510851                                             28/07/2008 07:59:48
     510852                                             28/07/2008 07:59:55
     510853                                             28/07/2008 08:00:01
    Regards,

    Chiappa

  3. #3
    Join Date
    Jul 2008
    Posts
    7
    Hi,
    Code:
    SQL> select * from test_detail where RECEIPT_NO between 1 and 4;
    
    RECEIPT_NO                                         DATED         STATUS
    -------------------------------------------------- --------- ----------
    1                                                  28-JUL-08
    2                                                  28-JUL-08
    3                                                  28-JUL-08
    4                                                  28-JUL-08
    
    SQL> select * from test_detail;
    
    RECEIPT_NO                                         DATED         STATUS
    -------------------------------------------------- --------- ----------
    1                                                  28-JUL-08
    2                                                  28-JUL-08
    3                                                  28-JUL-08
    4                                                  28-JUL-08
    5                                                  28-JUL-08

  4. #4
    Join Date
    Jul 2008
    Posts
    7
    Hi Chiappa,

    Today i've learned the problem in converting the datatypes.

    But i tried the following but working properly.
    Is it like in some cases????
    Code:
    SQL> select * from test_detail
      2     where receipt_no between '510851' and '510900';
    
    RECEIPT_NO                                         DATED         STATUS
    -------------------------------------------------- --------- ----------
    510851                                             28-JUL-08
    510852                                             28-JUL-08
    510853                                             28-JUL-08
    51088                                              28-JUL-08
    51089                                              28-JUL-08
    51090                                              28-JUL-08
    
    6 rows selected.
    
    SQL> select * from test_detail
      2  where receipt_no between 510851 and 510900;
    
    RECEIPT_NO                                         DATED         STATUS
    -------------------------------------------------- --------- ----------
    510851                                             28-JUL-08
    510852                                             28-JUL-08
    510853                                             28-JUL-08

  5. #5
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Problem is, when you do NOT demand the correct datatype, the database will choose, ie, an IMPLICT conversion will occur - you have a string column a nd a numeric between, SO the Oracle db can choose convert the column to number (and the logic will work), OR it can choose convert the number between to string, thus bringing back the problem. The answer is ONE, do EXPLICITALY what you want.

    Regards,

    Chiappa

  6. #6
    Join Date
    Sep 2008
    Posts
    1
    I have values like 'A', 'B','C','D' in a varchar field. I want to get all values between A and D. how to write a query?

  7. #7
    Join Date
    Apr 2006
    Posts
    377
    Did you even try based off the information provided in the posts above?

    Code:
    SQL> select * from t1;
    
    A1
    --------------------
    A
    B
    C
    D
    M
    Z
    
    6 rows selected.
    
    SQL> select * from t1
      2  where a1 between 'A' and 'D';
    
    A1
    --------------------
    A
    B
    C
    D

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width