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