Fast Word Capping - MySQL
Posted: Fri Feb 05, 2010 9:23 am
I've been handed this MySQL database to create a listing. The problem is, every single entry is in uppercase. Everything. This wouldn't be a problem except that the client wants the text to appear "regular" with the first word in each sentence capitalized and the rest lowercase. This is a pretty intensive query for a simple listing - 3 full seconds to return the 10 entries required.
Capping only the first letter isn't that big of a deal. Unfortunately there are several fields that are "title" fields, where each word needs to be capitalized. I wrote the following MySQL function to handle it but running it stretches my query out to 7 - 8 seconds which I'm guessing is going to be an unacceptable amount of time. Anybody got any suggestions on how I can speed this up, either through code optimization or a different solution?
Capping only the first letter isn't that big of a deal. Unfortunately there are several fields that are "title" fields, where each word needs to be capitalized. I wrote the following MySQL function to handle it but running it stretches my query out to 7 - 8 seconds which I'm guessing is going to be an unacceptable amount of time. Anybody got any suggestions on how I can speed this up, either through code optimization or a different solution?
Code: Select all
DELIMITER $$
DROP FUNCTION IF EXISTS `CAP_FIRST` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `CAP_FIRST`(input VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
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 $$
DELIMITER ;