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
);