20 Oct 08 MySQL Capitalize Function

I wrote a little MySQL function to capitalize the first letter of every word in a string. I thought I'd share since I wasn't able to google for it.

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

I would rather have regex'd it, but I couldn't find any sort of regex replace function in the docs.

Download the code!

Tags: mysql

Filed in code

Reader's Comments

1. Capitalize the first letter of each word using MYSQL - Microsoft Windows Vista Community Forums - Vistaheads | 04 Nov 2008 11:18 pm

   [...] Re: Capitalize the first letter of each word using MYSQL That only does the first word. I wrote a little function to do it : MySQL Capitalize Function [...]

2. Muhammad Abubakar | 29 Dec 2008 4:04 am

   AOA
   Thanks Friend for this.

   Here is my modified version.

   DELIMITER $$

   DROP FUNCTION IF EXISTS `sharex`.`Capitalize`$$

   CREATE DEFINER=`root`@`%` FUNCTION `Capitalize`(InputString VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8
   BEGIN
   DECLARE Input_Length INT;
   DECLARE Count INT;

   SET Input_Length = CHAR_LENGTH(InputString);
   SET InputString = LOWER(InputString);
   SET Count = 0;

   WHILE (Count < Input_Length) DO
   IF (MID(InputString,Count,1) = ‘ ‘ OR MID(InputString,Count,1) = ‘.’ OR Count = 0) THEN
   SET InputString = CONCAT(LEFT(InputString,Count),UPPER(MID(InputString,Count + 1,1)),
   RIGHT(InputString,Input_Length – Count – 1));
   ELSE
   SET InputString = CONCAT(LEFT(InputString,Count),LOWER(MID(InputString,Count + 1,1)),
   RIGHT(InputString,Input_Length – Count – 1));
   END IF;
   SET Count = Count + 1;
   END WHILE;

   RETURN InputString;

   END$$

   DELIMITER ;

3. Capitalize the first letter of each word using MYSQL | keyongtech | 18 Jan 2009 11:01 am

   [...] That only does the first word. I wrote a little function to do it : ‘MySQL Capitalize Function’ (http://joezack.com/index.php/2008/10…lize-function/) — thejoezack Posted via http://www.vistaheads.com « [...]

4. Ixalmida | 28 Aug 2009 10:30 am

   Awesome function JOEZACK! Works like a charm. Thanks for sharing!

5. alan | 25 Nov 2009 7:09 pm

   JOEZACK thanks mate, needed this exact thing and works perfect.

   thanks again

6. Jeremy Woertink | 09 Mar 2010 1:09 pm

   DUDE! This layout is awesome. I’m diggin’ the chrome style. Oh, and on a side note, you’re a sick sick individual for writing SQL like that :p lol. Thanks, this is going to help out