UDF INITCAP in MySQL

Title

Description

Most SQL dialects have a way to turn a string like this: "tHis STRING" to this: "This String" where each word starts with a capitalized letter. This is useful for transforming names of people or places (e.g. ‘North Carolina’ or ‘Juan Martin Del Potro’.

Per this stackoverflow post, this UDF will give us INITCAP functionality.

DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`initcap`$$

CREATE FUNCTION `initcap`(x char(30)) RETURNS char(30) CHARSET utf8
READS SQL DATA
DETERMINISTIC
BEGIN
SET @str='';
SET @l_str='';
WHILE x REGEXP ' ' DO
SELECT SUBSTRING_INDEX(x, ' ', 1) INTO @l_str;
SELECT SUBSTRING(x, LOCATE(' ', x)+1) INTO x;
SELECT CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(@l_str,1,1)),LOWER(SUBSTRING(@l_str,2)))) INTO @str;
END WHILE;
RETURN LTRIM(CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(x,1,1)),LOWER(SUBSTRING(x,2)))));
END$$

DELIMITER ;


Usage: 

select initcap('This is test string');

Hey, thanks for this. I added it to the GitHub repo too: sql-snippets/initcap-udf.md at main · count/sql-snippets · GitHub