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

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

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

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

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

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?

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