SQL - Where criteria to find names between A-F

Question

Simple question:

I need a solution so that I can find, lets say names, between A-F, INCLUDING all names that start with F.

If you use BETWEEN or A >= value <= F you find out that it stops at F. So I am posting this for suggestions.

NOTE: User will see 2 textboxes that accept a range user can type. The user refines how far to go in F boundary as such: User types in 'Fa' means the result should return: Fauder, Fail, Famber, ... etc

I have currently 2 solutions but there's got a be a better way.

Solution 1: This will add 1 to outer boundary but may include result if there's a name that is single 'G', highly unlikely though. WHERE name >= 'A' AND <= CHAR(ASCII('F') + 1)

Solution 2: This solution appends last letter of alphabet field length times. WHERE name >= 'A' AND <= 'FZZZZZZZZZZZZZZZZZZZZZ'

Although the above solutions are workable, my search can be refined as such: A to Fs (should give me everything from A to and including Fs....). With this solution #1 is broken since it works with single ascii.

Suggestions are welcome.

Answer 1

You can do:

WHERE name >= 'A' AND name < 'G'

Answer 2

How about this?

WHERE SUBSTR(name, 1, 1) >= 'A' AND SUBSTR(name, 1, 1) <= 'F'