-
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;
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|