You Asked

Hi Team,

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.

Example:

select * from my_table where column_name between '1890' and '1891';

Data fetched:

column_name datatype: varchar2

Can you please help me on how to solve this. Thanks in advance.

Thanks
Paresh Mundra

and Chris said...

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:

1890anyothercharactershere

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;
  
V      
1890    
1891

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Paresh Mundra, February 26, 2020 - 1:29 pm UTC

Hi Team,

Thanks for update but it is still not working. Please use below queries. I have tried to replicate the issue using sample data.

create table tab_serial_number
(serial_number varchar2(300));

insert into tab_serial_number values ('1890');
insert into tab_serial_number values ('ABCDte134');
insert into tab_serial_number values ('18900');
insert into tab_serial_number values ('189008');
insert into tab_serial_number values ('189009');
insert into tab_serial_number values ('18901');
insert into tab_serial_number values ('189010');
insert into tab_serial_number values ('189011');
insert into tab_serial_number values ('1891');

with rws as (
select serial_number from tab_serial_number
)
select * from rws
where to_number( serial_number ) between '1890' and '1891';

Using above query gives error: Invalid Number

with rws as (
select serial_number from tab_serial_number
)
select * from rws
where ( serial_number ) between '1890' and '1891';

using above query, same result.

February 27, 2020 - 11:21 am UTC

Sounds like you need to clean your data ;)

But if you genuinely do store alphanumeric values and want to numeric filtering, you could find all the numeric values first with a regex or similar.

Then use between on the numbers:

with rws as (
  select serial_number , rownum
  from   tab_serial_number
  where  regexp_like ( serial_number, '^[0-9]+$' )
)
select * from rws
where  to_number( serial_number ) between 1890 and 1891;

SERIAL_NUMBER   ROWNUM   
1890                     1 
1891                     8

The rownum in the subquery is to stop the optimizer merging the conditions together. i.e. guarantee is does the regex before to_number.

Chuck Jolley, February 26, 2020 - 10:48 pm UTC

try

where lpad(column, 32, '0') between lpad(:start, 32, '0') and lpad(:end, 32, '0')

PS This assumes the column doesn't contain data more than 32 char wide. If it does then just widen the lpad.

A reader, February 27, 2020 - 6:28 am UTC

Tried select distinct serial_number from tab_serial_number where lpad(serial_number,80,'0') between lpad('1890',80,'0') and lpad('1891',80,'0');

Works for this.

But for below set of data, it doesn't.

insert into tab_serial_number values ('500');
insert into tab_serial_number values ('501');
insert into tab_serial_number values ('502');
insert into tab_serial_number values ('503');
insert into tab_serial_number values ('504');
insert into tab_serial_number values ('505');
insert into tab_serial_number values ('506');
insert into tab_serial_number values ('507');
insert into tab_serial_number values ('508');
insert into tab_serial_number values ('509');
insert into tab_serial_number values ('510');
insert into tab_serial_number values ('511');
insert into tab_serial_number values ('512');
insert into tab_serial_number values ('513');
insert into tab_serial_number values ('514');
insert into tab_serial_number values ('515');
insert into tab_serial_number values ('516');
insert into tab_serial_number values ('517');
insert into tab_serial_number values ('518');
insert into tab_serial_number values ('519');
insert into tab_serial_number values ('520');
insert into tab_serial_number values ('521');
insert into tab_serial_number values ('522');
insert into tab_serial_number values ('523');
insert into tab_serial_number values ('524');
insert into tab_serial_number values ('525');
insert into tab_serial_number values ('526');
insert into tab_serial_number values ('527');
insert into tab_serial_number values ('528');
insert into tab_serial_number values ('529');
insert into tab_serial_number values ('530');
insert into tab_serial_number values ('531');
insert into tab_serial_number values ('532');
insert into tab_serial_number values ('533');
insert into tab_serial_number values ('534');
insert into tab_serial_number values ('535');
insert into tab_serial_number values ('536');
insert into tab_serial_number values ('537');
insert into tab_serial_number values ('538');
insert into tab_serial_number values ('539');
insert into tab_serial_number values ('540');
insert into tab_serial_number values ('541');
insert into tab_serial_number values ('542');
insert into tab_serial_number values ('543');
insert into tab_serial_number values ('544');
insert into tab_serial_number values ('545');
insert into tab_serial_number values ('546');
insert into tab_serial_number values ('547');
insert into tab_serial_number values ('548');
insert into tab_serial_number values ('549');
insert into tab_serial_number values ('550');
insert into tab_serial_number values ('551');
insert into tab_serial_number values ('552');
insert into tab_serial_number values ('553');
insert into tab_serial_number values ('554');
insert into tab_serial_number values ('555');
insert into tab_serial_number values ('556');
insert into tab_serial_number values ('557');
insert into tab_serial_number values ('558');
insert into tab_serial_number values ('559');
insert into tab_serial_number values ('560');
insert into tab_serial_number values ('561');
insert into tab_serial_number values ('562');
insert into tab_serial_number values ('563');
insert into tab_serial_number values ('564');
insert into tab_serial_number values ('565');
insert into tab_serial_number values ('566');
insert into tab_serial_number values ('567');
insert into tab_serial_number values ('568');
insert into tab_serial_number values ('569');
insert into tab_serial_number values ('570');
insert into tab_serial_number values ('571');
insert into tab_serial_number values ('572');
insert into tab_serial_number values ('573');
insert into tab_serial_number values ('574');
insert into tab_serial_number values ('575');
insert into tab_serial_number values ('576');
insert into tab_serial_number values ('577');
insert into tab_serial_number values ('578');
insert into tab_serial_number values ('579');
insert into tab_serial_number values ('580');
insert into tab_serial_number values ('581');
insert into tab_serial_number values ('582');
insert into tab_serial_number values ('583');
insert into tab_serial_number values ('584');
insert into tab_serial_number values ('585');
insert into tab_serial_number values ('586');
insert into tab_serial_number values ('587');
insert into tab_serial_number values ('588');
insert into tab_serial_number values ('589');
insert into tab_serial_number values ('590');
insert into tab_serial_number values ('591');
insert into tab_serial_number values ('592');
insert into tab_serial_number values ('593');
insert into tab_serial_number values ('594');
insert into tab_serial_number values ('595');
insert into tab_serial_number values ('596');
insert into tab_serial_number values ('597');
insert into tab_serial_number values ('598');
insert into tab_serial_number values ('599');
insert into tab_serial_number values ('5LH');
insert into tab_serial_number values ('5MA');
insert into tab_serial_number values ('600');

select distinct serial_number from tab_serial_number where lpad(serial_number,80,'0') between lpad('500',80,'0') and lpad('600',80,'0');

Considering my column maximum range is 80 characters.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

SQL

Between function not working on varchar2 column

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

SQL