Can you compare text alphabetically in a WHERE clause?

Question

I'm using SQLite, and I need to do the following:

SELECT * FROM fruit WHERE name<='banana'

This statement should return all entries whose "name" column contains text that comes alphabetically before (or is equal to) the word "banana". So it should return the row with "apple", but not the row with "pear" or "orange".

It appears that simply using the <= operator doesn't work, so is there another way?

Answer 1

It should work. But beware: comparison is binary. 'A' < 'a' in ASCII. To compare alphabetically, without sensitivity to case, you should do wHERE LOWER(my_column) < 'value'