How do I search for a subset in a last name field?

Question

I have a set of students at a high school. The counselors want to divide the students up by last name. Here is the break down:

Counselor 1: A to F Counselor 2: G to Hr Counselor 3: Hs to O Counselor 4: P - Z

The first one is easy, I just do a:

where last_name like '[A-F]%'

but the second counselor is giving me grief, because if I do:

where last_name like '[G-Hr]%'

...I get all students with last names of G, H, and R. What is the best way to get what I want for counselor's 2 and 3?

Answer 1

For counselor 2, try:

WHERE last_name LIKE 'G%' OR last_name LIKE 'H[a-r]%'

For counselor 3, try:

WHERE last_name LIKE 'H[s-z]%' OR last_name LIKE '[I-O]%'