capitalize function
Writing by corra on Tuesday, 7 of October , 2008 at 5:07 pm
Here is just a simple stored function to capitalize the first letter of each word in a string.
Sometimes it’is useful to convert name of cities and persons using the correct capitalization.
DROP FUNCTION IF EXISTS `capitalize`;
DELIMITER $$
CREATE FUNCTION `capitalize`(stringa TEXT) RETURNS text
DETERMINISTIC
BEGIN
DECLARE i INT DEFAULT 2;
DECLARE sout TEXT;
DECLARE ucn INT DEFAULT 0;
SET stringa=LCASE(TRIM(BOTH ' ' FROM stringa));
SET sout=UCASE(LEFT(stringa,1));
WHILE i <= LENGTH(stringa) DO
SET sout = CONCAT(sout,IF(ucn=1,UCASE(SUBSTRING(stringa,i,
1)),SUBSTRING(stringa,i,1)));
SET ucn = IF(SUBSTRING(stringa,i,1)=' ',1,0);
SET i = i + 1;
END WHILE;
RETURN sout;
END
$$
Example:
mysql> SELECT capitalize(' new york ');
+--------------------------+
| capitalize(' new york ') |
+--------------------------+
| New York |
+--------------------------+
Comments (3)
Category: MySQL,MySQLen
3 Comments
Comment by mike
Made Tuesday, 7 of October , 2008 at 8:28 pm
you can always use php’s ucfirst() and ucwords() 
Comment by Shantanu Oak
Made Sunday, 12 of October , 2008 at 1:07 pm
This seems to be interesting.
Is it better than the code that can be found here?
http://forge.mysql.com/tools/tool.php?id=158
Comment by corra
Made Sunday, 12 of October , 2008 at 2:37 pm
I don’t know. The two solutions are different.
It needs some test.
Leave a comment
