[Panoy]

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.

[r937]

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

[Panoy]

I will be trying out solution #1. I think its more optimized than my own query. Thanks again