1

condition in below query returning true result but i want false result. Please suggest me.

select *
from test    
where 
'00160001' between '0013001' and '0023000' OR 
'00200000' between '0013001' and '0023000'
Álvaro González
  • 142,137
  • 41
  • 261
  • 360
user3603284
  • 43
  • 1
  • 13
  • What is preventing you from using actual numbers or converting the values to numbers? – Vatev May 30 '16 at 15:00
  • I assume that you actually want to test columns in the table against those values? Currently it will return all the rows in the table or none depending purely on the constants you provide as you don't test any existing columns in the table. If you do decide to check the data in the table then please show us the column definitions so we know whether they are strings or numbers. – Ryan Vincent May 30 '16 at 15:10
  • As you defined all the literals as strings then all the compares will be strings. By definition, An 8 character string cannot be between a 7 character string unless some extra clues are provided. – Ryan Vincent May 30 '16 at 15:28

1 Answers1

0

You get the same result without the leading zeros.
Use integer data type to compare integer values.
You can also convert the values on the fly using cast:

http://dev.mysql.com/doc/refman/5.7/en/cast-functions.html

Your example with Cast: 

select if( cast('00160001' as unsigned) between cast('0013001' as unsigned)  
and cast('0023000' as unsigned),1,0) as test_a,  
if( cast('00200000' as unsigned) between cast('0013001' as unsigned)   
and cast('0023000' as unsigned),1,0) as test_b;
iLikeMySql
  • 736
  • 3
  • 7