how to use BETWEEN for varchar field
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
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.
CREATE TABLE TEST_DETAIL
RECEIPT_NO VARCHAR2(50 BYTE) NOT NULL,
DATED DATE DEFAULT sysdate,
STATUS NUMBER DEFAULT NULL