Using 'between' operator for varchar columns

Hi,
I have a table with a varchar2 (80 char) column storing values 'sc51' through 'sc60'. When I run the following SQL I see all the values:

select vartest from vartest_tbl
where (upper(vartest) between upper('sc51') and upper('sc99') );

sc51
sc52
sc53
sc54
sc55
sc56
sc57
sc58
sc59
sc60

But when I run the following SQL I don't see any values:

select vartest from vartest_tbl
where (upper(vartest) between upper('sc51') and upper('sc100') );

And if I change the SQL to:

select vartest from vartest_tbl
where (upper(vartest) between upper('sc51') and upper('sc600') );

I see all the values again.

Using:

select vartest from vartest_tbl
where (upper(vartest) between upper('sc51') and upper('sc599') );

shows me values between 'sc51' - 'sc59' only.

Can you explain this behavior? What do I need to do to get the correct queryset every time?

Thank you.

its because it is just text, it doesn't understand you have 2 letters then 3 numbers

so nothing is between sc51 and sc100 as 1 comes before 5 in the checking, so nothing returned, when you select 600 it works as 6 comes after 5

you will need to split the value up based on the 3rd character and above in your example

You could split the column into a two character code, and a separate column that would be a number.
i.e. where type='SC' and code between 51 and 100;

Thank you.