MySQL – Select where first character is lowercase or uppercase

Sorry if this is something that was asked before, but I am not able to find the answer to this. How do I create a MySQL select statement to select all strings from the database whose first characters is either in lowercase or uppercase.

I’d normally use something like this to select 10 random words from English Word Database with length of more than 8 characters:

SELECT word FROM en_dictionary WHERE CHAR_LENGTH(word)>8 ORDER BY RAND() LIMIT 10

Unfortunately, this also selects the words in Capital letters that I don’t want in the results, as they are usually the terms such as names of cities, species etc.:

So, my question is: Is there a way to select only the words that start with the lowercase alphabet characters (or uppercase)?

I could do something like this and it’d work, but it’d be a very ugly statement:

AND word LIKE BINARY 'a%' AND word LIKE BINARY 'b%' AND word LIKE BINARY 'c%'...

Answer

You can use Ascii() function. It returns the numeric value of the leftmost character of the input string.

For lowercase first character: ASCII code of a is 97 and z is 122. So, the ASCII code of the first character should be Between 97 and 122.

SELECT word 
FROM en_dictionary 
WHERE CHAR_LENGTH(word) > 8 
  AND ASCII(word) BETWEEN 97 AND 122
ORDER BY RAND() LIMIT 10

---

For uppercase first character: ASCII code of A is 65 and Z is 90. So, the ASCII code of the first character should be Between 65 and 90.

SELECT word 
FROM en_dictionary 
WHERE CHAR_LENGTH(word) > 8 
  AND ASCII(word) BETWEEN 65 AND 90
ORDER BY RAND() LIMIT 10