有没有简单的方法可以将MySQL数据转换为Title Case？?mysql

尤里卡！从字面上看，我的第一个SQL函数。不提供保修。使用前备份数据。:)

首先，定义以下功能：

DROP FUNCTION IF EXISTS lowerword; SET GLOBAL log_bin_trust_function_creators=TRUE; DELIMITER | CREATE FUNCTION lowerword( str VARCHAR(128), word VARCHAR(5) ) RETURNS VARCHAR(128) DETERMINISTIC BEGIN DECLARE i INT DEFAULT 1; DECLARE loc INT;

SET loc = LOCATE(CONCAT(word,' '), str, 2); IF loc > 1 THEN WHILE i <= LENGTH (str) AND loc <> 0 DO SET str = INSERT(str,loc,LENGTH(word),LCASE(word)); SET i = loc+LENGTH(word); SET loc = LOCATE(CONCAT(word,' '), str, i); END WHILE; END IF; RETURN str; END; | DELIMITER ; 这将降低在str中出现的所有单词。

然后定义此修改后的适当功能：

DROP FUNCTION IF EXISTS tcase; SET GLOBAL log_bin_trust_function_creators=TRUE; DELIMITER | CREATE FUNCTION tcase( str VARCHAR(128) ) RETURNS VARCHAR(128) DETERMINISTIC BEGIN DECLARE c CHAR(1); DECLARE s VARCHAR(128); DECLARE i INT DEFAULT 1; DECLARE bool INT DEFAULT 1; DECLARE punct CHAR(17) DEFAULT ' ()[]{},.-_!@;:?/'; SET s = LCASE( str ); WHILE i <= LENGTH( str ) DO BEGIN SET c = SUBSTRING( s, i, 1 ); IF LOCATE( c, punct ) > 0 THEN SET bool = 1; ELSEIF bool=1 THEN
BEGIN IF c >= 'a' AND c <= 'z' THEN
BEGIN SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1)); SET bool = 0; END; ELSEIF c >= '0' AND c <= '9' THEN SET bool = 0; END IF; END; END IF; SET i = i+1; END; END WHILE;

SET s = lowerword(s, 'A'); SET s = lowerword(s, 'An'); SET s = lowerword(s, 'And'); SET s = lowerword(s, 'As'); SET s = lowerword(s, 'At'); SET s = lowerword(s, 'But'); SET s = lowerword(s, 'By'); SET s = lowerword(s, 'For'); SET s = lowerword(s, 'If'); SET s = lowerword(s, 'In'); SET s = lowerword(s, 'Of'); SET s = lowerword(s, 'On'); SET s = lowerword(s, 'Or'); SET s = lowerword(s, 'The'); SET s = lowerword(s, 'To'); SET s = lowerword(s, 'Via');

RETURN s; END; | DELIMITER ; 用法

验证它是否按预期工作：

SELECT tcase(title) FROM table; 用它：

UPDATE table SET title = tcase(title); 来源：http：//www.artfulsoftware.com/infotree/queries.php？＆ bw = 1070＃122来源：stack overflow