Posted by Michael Alfaro on May 7, 2012

How to capitalize first letters of words in MySQL

Found the need for this on our Weatherwiseapp.com site, and wanted to share. Original source here: http://joezack.com/index.php/2008/10/20/mysql-capitalize-function/

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;

So running the following code…

SELECT	CAP_FIRST(
	'this is totally like   @ TEST 1 right!'
)

Returns the string “This Is Totally Like @ Test 1 Right!”

Topics: development, technology, website curation