MYSQL – Find Starting With Lowercase Letter
I recently wanted to update the taxonomy within a CMS to set the name of all tags to begin with an uppercase letter. Over 10+ years I have a messy mix of case sensitivities; I’ve chosen to set the slug to lowercase and the name to sentence case.
You can obviously manually do this within your CMS Admin, but wanted a faster way.
You can use the ASCII() function to return the numeric value of the leftmost character of the input string. For lowercase “a”, the ASCII code is 97. For lowercase “z”, the ASCII code is 122.
Therefore, we can write:
SELECT * FROM `table_name` WHERE CHAR_LENGTH(name) > 2 AND ASCII(name) BETWEEN 97 AND 122
We can then take it a step further and update the table to set sentence case in one fell swoop.
UPDATE `table_name` SET name = CONCAT(UCASE(LEFT(name, 1)), LCASE(SUBSTRING(name, 2))) WHERE CHAR_LENGTH(name) > 2 AND ASCII(name) BETWEEN 97 AND 122
Hopefully that is of use to you.
Leave a comment