sql like and between

Question

Look at these two queries

SELECT * 
FROM user 
WHERE user_name BETWEEN 'A' AND 'Z' 
ORDER  BY user_name

SELECT * 
FROM user 
WHERE user_name LIKE '[a-z]%'

Shouldn't they give the same answers?
The first one is returning answers 603 and second one is not returning anything

Answer 1

No, because the evil me can enter a username that starts with whitespace, or a username like Zina

That's not in the range A-Z ('Zina' for example would be in the range A-ZZ).
You'd probably get the (more or less) same results if you'd use

WHERE user_name BETWEEN 'A' AND 'ZZZZZZZZZZZZZZZZZZZ' 

Furthermore, it depends on the case-sensitivity of the collation.

The second query gets all usernames that start with a to z, but they may include other start characters, because there might just be some fancy turkish characters in that range (for real).

You'de be much better of using

WHERE user_name LIKE '[abcdefghijklmnopqrstuvwxyz]%'

That's assuming you have ASCII-only usernames.
(That doesn't work for German äöü, for example)

Note:
Your last remark leads my to conclude your second query returns nothing.
You didn't indicate the RDBMS system type you use, but if you're using PostGreSQL, the reason why you get nothing in the second query is that postgre is case-sensitive, and in your between query, you use capital letters. I assume your usernames start with capital letters (Haha ;) )?

When PostGre and you want a case-insensitive query, use ILIKE:

WHERE user_name ILIKE '[a-z]%'

Answer 2

First statement get all user_name greater / equal than A and lower / equal Z (applying alphabetical sort)

The second get all user_name start a-z.

If you have these rows:

Angel

Berthold

Samantha

Vanessa

Zuma

The first query gets:

Angel

Berthold

Samantha

Vanessa

The second gets... nothing