How to use >= on a varchar2 datatype
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to use >= on a varchar2 datatype

  1. #1
    Join Date
    Dec 2012
    Posts
    3

    How to use >= on a varchar2 datatype

    I have a table which has a column called "CUSTOMER_ID" and datatype of varchar2.

    I need to select all the rows with a value greater than 1013986 so I try using:

    select * from TABLE.A1_Cust_Cont WHERE CUSTOMER_ID>'1013986' order by CUSTOMER_ID asc;

    But it selects all the rows in the table - including numbers smaller. I assume I need to let SQL know to look at the CUSTOMER_ID column as a number.

    Any help would be appreciated.

    Thanks

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,443

    Cool

    Oracle is behaving as expected. If you have a bad design, you get bad results.
    You could force "numeric" comparison by left padding the column with zeroes:
    Code:
    SELECT *
      FROM table_a1
     WHERE LPAD ( customer_id, 32, '0') >= LPAD ( '1013986', 32, '0')
     ORDER BY customer_id ASC
    /
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Dec 2012
    Posts
    3
    Exactly what I needed - thanks so much

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