Changing Columns to Proper Case in MySQL - Using InitCap() function

MySQL String functions have lots of capabilities to manipulate the columns the way you wish to display. But apparently MySQL does not have any inbuilt function to convert the string to Proper Case. Some people understand this term as Sentence Case or Capitalized. However, on MySQL Dev site I found this amazing function which is worth quoting since it does exactly that.

DELIMITER $$
DROP FUNCTION IF EXISTS `initcap` $$
CREATE FUNCTION `initcap`(x char(30)) RETURNS char(30) CHARSET utf8
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 of this function would be as follows:-

- SELECT initcap('new delhi') returns "New Delhi"

Saving the article for my reference and for a lot of other people who might be looking for something similar.

What this Blog is About!!!

Friends, welcome to my blog!! It has been really long time since I did any blogging. My last blog was way back in 2007 on one of my websites which I had shut down due to lack of time. But since very long I have felt a need to document a lot of things that interest me and put forward to the world my everyday learnings. As I keep on venturing more and more into various technologies, I keep stumbling across lots of challenges which are worth documenting for like minded people to come across and get the benefit.

People coming across this blog would mostly find the articles about Operating Systems (mostly Linux), HowTo's on various topics, Databases in general and Data Warehouse, Performance and Scalability of various Web Applications etc and lot of Design patterns revolving around various technologies. Hope you find the blog extremely useful!!!