Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007
    Posts
    77

    Question Unanswered: selecting records from a range of values

    Hi to all,

    I wanted to retrieve records that are within the range of 2 letters.

    Example:

    I want to retrieve the student's surnames that starts with A and ends with C. I tried to run a query that uses the BETWEEN keyword in my WHERE clause, but the results turned out to return only records from A to B only.

    The same output goes if you use the field>='A%' and field<='B%' which is the same as using the BETWEEN keyword on your query.

    What I did is just use the LEFT function of MySQL and compared it to the start letter and end letter, which gave me the results I wanted.

    My question is that if there is another better or efficient way to do this? What if some other RDBMS does not support the LEFT function?

    I want to ask also why the BETWEEN keyword does not return records that start with the letter C in my query, but does retrieve records that start with letter A. Which I have specified in my BETWEEN query such as this:

    Ex: BETWEEN 'A%' and 'C%'

    Thanks and god bless.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it is entirely possible that there might be a database system somewhere that does not have the LEFT function (in that case, look for the SUBSTRING function, which is part of the SQL standard)

    here are a bunch of names --

    Adams
    Applethwaite
    Babson
    Boweridge
    Collins
    Coulter

    when you say BETWEEN 'A' and 'C', or the equivalent, >= 'A' and <= 'C', Collins and Coulter are not included because both are greater than 'C'

    if you want to use BETWEEN for this, then you have two choices --

    1. WHERE LEFT(name,1) BETWEEN 'A' AND 'C'

    2. WHERE name BETWEEN 'A' AND 'D' (assuming there is no person with the actual one-letter surname 'D')

    note also that wildcards will only work with LIKE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    77
    I will be trying out solution #1. I think its more optimized than my own query. Thanks again

Posting Permissions

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