MYSQL – Find Starting With Lowercase Letter

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.

Mike250

Australian. Sport. Passionate Cricket Fan. Go Pro. Abseiling. Snorkeling. Travel. Golf R. SQL Server Developer. Three sons. One daughter. Last Trip: New York.

Related Posts
Leave a comment

Your email address will not be published. Required fields are marked *