Capitalize the first letter of every word in MySql query

Capitalize the first letter of every word in MySql query

A simple MySQL function to capitalize the first letter of every word in a string.

CREATE FUNCTION CAP_FIRST (input VARCHAR(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE len INT;
DECLARE i INT;
SET len = CHAR_LENGTH(input);
SET input = LOWER(input);
SET i = 0;
WHILE (i < len) DO
IF (MID(input,i,1) = ' ' OR i = 0) THEN
IF (i < len) THEN
SET input = CONCAT(
LEFT(input,i),
UPPER(MID(input,i + 1,1)),
RIGHT(input,len - i - 1)
);
END IF;
END IF;
SET i = i + 1;
END WHILE;
RETURN input;
END;

Run this query which will create the function. Try to fetch the records with select query.

SELECT CAP_FIRST(authors.`name`) FROM authors;

Update all the table records with first letter capital.

UPDATE authors SET name = CAP_FIRST(authors.`name`);

You can also use a mysql build in function to update records but it will only update the first letter of whole string.

UPDATE authors SET name = concat( upper(substring(authors.`name`,1,1)),lower(substring(authors.`name`,2)) )

 

Share this post

Leave a Reply

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