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

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
/

---

:rolleyes:

Exactly what I needed - thanks so much