DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Using 'between' operator for varchar columns

  1. #1
    Join Date
    Sep 2013
    Posts
    2

    Question 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.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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;

  4. #4
    Join Date
    Sep 2013
    Posts
    2
    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width