I would like to use “between” operator on “varchar” and “char(5)” columns in a table. However, I don’t see any effect of “between” operator in the results.
Please note that “code” is the column name
I have a table with a column of type VARCHAR and I apply cast operation to convert it into “CHAR(5)” type. When I apply between operator in the query on either of the columns, it doesn’t produce a filtered result.
For VARCHAR COLUMN
select code from diagnoses where code between '4254' and '5855'
For CHAR(5) COLUMN
select cast(code as char(5)) as code from diagnoses where code between '4254' and '5855'
I expect the output to have only records within this range but instead I get records which don’t meet this criteria as well. Please find a sample screenshot below
BETWEEN operator for character based types works for alphabetical order. That means it uses the order
4254 45829 486 ... 58381 5845 5855
So the output is completely correct.
If you want a numeric order you have to cast it into an appropriate data type:
select cast(code as char(5)) as code from diagnoses where cast(code as int) between 4254 and 5855 -- cast here
4254 5119 5589 5845 5855