0

How to capitalize first letters of words in MySQL

By Michael Alfaro on May 7, 2012
Share on Facebook0Tweet about this on Twitter1Share on LinkedIn1Google+0

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!”