We are facing issue while using Between function in our SQL query. We are trying to use Between function on a column which is of datatype VARCHAR2. Data insert in this column can be alphanumeric as well as numeric, But it seems this function is not working properly. We cannot change the column datatype as well as cannot restrict to enter only character/numeric data.
select * from my_table where column_name between '1890' and '1891';
column_name datatype: varchar2
Can you please help me on how to solve this. Thanks in advance.
The BETWEEN condition is working exactly as advertised. The problem is you're comparing string values, not numbers!
BETWEEN is shorthand for:
where v >= '1890'
and v <= '1891'
And the string:
Is true for both these conditions.
This is because the database compares character values position by position. So
1890 < 1890anyothercharactershere, because the first four characters are the same. 1890 has no fifth character, but the other string does. So it's considered larger.
1891 > 1890anyothercharactershere, because the first three characters are the same, and the fourth is greater.
Assuming the column stored all numeric values, the correct solution is to change its data type to number.
This is easier said than done. So in the meantime:
- To_number the string
- Compare to numeric values:
with rws as (
select '1890' v from dual union all
select '18901' v from dual union all
select '18902' v from dual union all
select '18903' v from dual union all
select '1891' v from dual union all
select '18910' v from dual union all
select '18911' v from dual
select * from rws
where to_number ( v ) between 1890 and 1891;