Thursday, February 24, 2011

Mysql initCaps Function

0 comments


Initcap takes a string as input and returns string with each word's first character in uppercase and the rest in lowercase.

Input: varchar(255)
Output: varchar(255)

-------------------
Source Code:

-------------------

delimiter //
DROP FUNCTION IF EXISTS initCaps //
CREATE FUNCTION initCaps (prm_strInput varchar(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE nNumSpaces,spacePos1,spacePos2,v_strLen SMALLINT;
DECLARE i INT DEFAULT 0;
DECLARE v_parseStr varchar(255) DEFAULT ' ';
SET spacePos1 = 0;
SET spacePos2 = 1;
SET prm_strInput = TRIM(prm_strInput);
SET nNumSpaces = LENGTH(prm_strInput) -  LENGTH(REPLACE(prm_strInput,' ',''));
SET prm_strInput = CONCAT(' ',prm_strInput,' ');
WHILE (i <= nNumSpaces)  DO
 
   SET spacePos1 = LOCATE(' ',prm_strInput,spacePos1 + 1);
 
   SET spacePos2 = LOCATE(' ',prm_strInput,spacePos1 + 1);
   SET v_strLen = (spacePos2-spacePos1) -1;
   IF v_strLen > 0 THEN
     SET v_parseStr = CONCAT(v_parseStr, UPPER(SUBSTRING(prm_strInput,spacePos1 + 1,1)) , lower(SUBSTRING(prm_strInput,spacePos1 + 2,v_strLen -1)),' ');
  
   END IF;
   SET i = i + 1;
END WHILE;
 
   return trim(v_parseStr);
END
//

----------------------

Usage Example:
----------------------
mysql> select initCaps('Ahmad iftekhar RUMMAN') as c;
Ahmad Iftekhar Rumman

Leave a Reply

Labels