How can I capitalise each word in a MySQL text string without using PHP

This function below will capitalise each word in the string, in the same way that UCWORDS works in PHP.

Just copy this function code and run it at the Mysql command line prompt.

DELIMITER $$

DROP FUNCTION IF EXISTS `ICap`$$

CREATE FUNCTION `ICap`(mystring varchar(1000))
RETURNS VARCHAR(1000)
BEGIN

DECLARE i INT DEFAULT 1;
DECLARE myc, pc CHAR(1);
DECLARE myoutstring VARCHAR(1000) DEFAULT LOWER(mystring);

WHILE i <= CHAR_LENGTH(mystring) DO
SET myc = SUBSTRING(mystring, i, 1);
SET pc = CASE WHEN i = 1 THEN ‘ ‘ ELSE SUBSTRING(mystring, i – 1, 1) END;
IF pc IN (‘ ‘, ‘&’, ””, ‘_’, ‘?’, ‘;’, ‘:’, ‘!’, ‘,’, ‘-‘, ‘/’, ‘(‘, ‘.’) THEN
SET myoutstring = INSERT(myoutstring, i, 1, UPPER(myc));
END IF;
SET i = i + 1;
END WHILE;

RETURN myoutstring;

END$$

DELIMITER ;

Then use SELECT ICap(‘test test’); to get back ‘Test Test’ – you could even call the function “ucwords” if you want to make it easier to remember.