有没有一种简单的方法可以将 MySQL 数据转换为 Title Case？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1191605/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a simple way to convert MySQL data into Title Case?
提问by John Stephens
I have a MySQL table where all the data in one column was entered in UPPERCASE, but I need to convert in to Title Case, with recognition of "small words" akin to the Daring Fireball Title Case script.
我有一个 MySQL 表，其中一列中的所有数据都以大写形式输入，但我需要转换为标题案例，并识别类似于Daring Fireball Title Case 脚本的“小词” 。
I found this excellent solutionfor transforming strings to lowercase, but the Title Case function seems to have been left out of my version of MySQL. Is there an elegant way to do this?
我发现了这个将字符串转换为小写的优秀解决方案，但 Title Case 函数似乎已被排除在我的 MySQL 版本之外。有没有一种优雅的方法来做到这一点？
采纳答案by John Stephens
Woo! I'm not handy with SQL at all; Here's the method that worked for me:
哇！我根本不擅长使用 SQL；这是对我有用的方法：
- Export the table as a text file in .sql format.
- Open the file in Textmate(which I already had handy).
- Select the rows with UPPERCASE data.
- Choose "Convert" from the "Text" menu, and select "to Titlecase".
Find and replace each instance of:
INSERT INTO `Table` (`Col1`, `Col2`, `Etc`, ...) VALUESwith the correct lowercase values.
- Import the table back into the database.
- Use
UPDATE table SET colname=LOWER(colname);to reset lowercase values for the columns that should be lowercase.
- 将表导出为 .sql 格式的文本文件。
- 在Textmate 中打开文件（我已经很方便了）。
- 选择带有大写数据的行。
- 从“文本”菜单中选择“转换”，然后选择“到 Titlecase”。
查找并替换以下每个实例：
INSERT INTO `Table` (`Col1`, `Col2`, `Etc`, ...) VALUES使用正确的小写值。
- 将表导入回数据库。
- 使用
UPDATE table SET colname=LOWER(colname);为，应该是小写的列重置小写值。
The reason I didn't try using Textmate before was that I couldn't figure out how to convert a single column to Title Case without ruining the other data, but this method seems to work. Thanks for your guidance and support!
我之前没有尝试使用 Textmate 的原因是我无法弄清楚如何在不破坏其他数据的情况下将单个列转换为 Title Case，但这种方法似乎有效。感谢您的指导和支持！
回答by hobodave
Edit
编辑
Eureka! Literally my first SQL function. No warranty offered. Back up your data before using. :)
尤里卡！从字面上看，我的第一个 SQL 函数。不提供保修。使用前请备份您的数据。:)
First, define the following function:
首先，定义以下函数：
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 ;
This will lower all occurrences of word in str.
这将降低 str 中出现的所有单词。
Then define this modified proper function:
然后定义这个修改后的适当函数：
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 ;
Usage
用法
Verify it works as expected:
验证它是否按预期工作：
SELECT tcase(title) FROM table;
Use it:
用它：
UPDATE table SET title = tcase(title);
Source: http://www.artfulsoftware.com/infotree/queries.php?&bw=1070#122
来源：http: //www.artfulsoftware.com/infotree/queries.php?&bw= 1070#122
回答by Anupam
umm something like this may work
嗯，这样的事情可能会奏效
UPDATE table_name SET `col_name`= CONCAT( UPPER( SUBSTRING( `col_name`, 1, 1 ) ) , LOWER( SUBSTRING( `col_name` FROM 2 ) ) );
回答by CurtainDog
If you need to throw custom acronyms and other custom capitalisation patterns into the mix I've generalised hobodave's answer:
如果您需要将自定义首字母缩略词和其他自定义大写模式混合在一起，我已经概括了 hobodave 的答案：
DELIMITER |
CREATE FUNCTION replaceword( str VARCHAR(128), word VARCHAR(128) )
RETURNS VARCHAR(128)
DETERMINISTIC
BEGIN
DECLARE loc INT;
DECLARE punct CHAR(27) DEFAULT ' ()[]{},.-_!@;:?/''"#$%^&*<>';
DECLARE lowerWord VARCHAR(128);
DECLARE lowerStr VARCHAR(128);
IF LENGTH(word) = 0 THEN
RETURN str;
END IF;
SET lowerWord = LOWER(word);
SET lowerStr = LOWER(str);
SET loc = LOCATE(lowerWord, lowerStr, 1);
WHILE loc > 0 DO
IF loc = 1 OR LOCATE(SUBSTRING(str, loc-1, 1), punct) > 0 THEN
IF loc+LENGTH(word) > LENGTH(str) OR LOCATE(SUBSTRING(str, loc+LENGTH(word), 1), punct) > 0 THEN
SET str = INSERT(str,loc,LENGTH(word),word);
END IF;
END IF;
SET loc = LOCATE(lowerWord, lowerStr, loc+LENGTH(word));
END WHILE;
RETURN str;
END;
|
DELIMITER ;
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(27) 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 = replaceword(s, 'a');
SET s = replaceword(s, 'an');
SET s = replaceword(s, 'and');
SET s = replaceword(s, 'as');
SET s = replaceword(s, 'at');
SET s = replaceword(s, 'but');
SET s = replaceword(s, 'by');
SET s = replaceword(s, 'for');
SET s = replaceword(s, 'if');
SET s = replaceword(s, 'in');
SET s = replaceword(s, 'n');
SET s = replaceword(s, 'of');
SET s = replaceword(s, 'on');
SET s = replaceword(s, 'or');
SET s = replaceword(s, 'the');
SET s = replaceword(s, 'to');
SET s = replaceword(s, 'via');
SET s = replaceword(s, 'RSS');
SET s = replaceword(s, 'URL');
SET s = replaceword(s, 'PHP');
SET s = replaceword(s, 'SQL');
SET s = replaceword(s, 'OPML');
SET s = replaceword(s, 'DHTML');
SET s = replaceword(s, 'CSV');
SET s = replaceword(s, 'iCal');
SET s = replaceword(s, 'XML');
SET s = replaceword(s, 'PDF');
SET c = SUBSTRING( s, 1, 1 );
IF c >= 'a' AND c <= 'z' THEN
SET s = CONCAT(UCASE(c),SUBSTRING(s,2));
END IF;
RETURN s;
END;
|
DELIMITER ;
Essentially it consists of a case-insensitive word replace function and a function to capitalise the first letter of every word and perform some transforms for specific words.
本质上，它由一个不区分大小写的单词替换函数和一个将每个单词的第一个字母大写并为特定单词执行一些转换的函数组成。
Hope its helpful to someone.
希望它对某人有帮助。
回答by Manuel Lopera
My solution for simple proper case:
我的简单适当案例的解决方案：
CREATE FUNCTION `proper_case`(str varchar(128)) RETURNS varchar(128)
BEGIN
DECLARE n, pos INT DEFAULT 1;
DECLARE sub, proper VARCHAR(128) DEFAULT '';
if length(trim(str)) > 0 then
WHILE pos > 0 DO
set pos = locate(' ',trim(str),n);
if pos = 0 then
set sub = lower(trim(substr(trim(str),n)));
else
set sub = lower(trim(substr(trim(str),n,pos-n)));
end if;
set proper = concat_ws(' ', proper, concat(upper(left(sub,1)),substr(sub,2)));
set n = pos + 1;
END WHILE;
end if;
RETURN trim(proper);
END
Use it as:
将其用作：
SELECT proper_case("JOHN DOE");
Output:
输出：
John Doe
回答by hriziya
I used something like this
我用过这样的东西
UPDATE `tablename`
SET `fieldname` = CONCAT(UCASE(SUBSTRING(`fieldname`,1,1)),'', LCASE(SUBSTRING(`fieldname`,2,LENGTH(`fieldname`))))
Note: This will only convert the first character to uppercase. and rest of the value to lowercase.
注意：这只会将第一个字符转换为大写。和其余的值小写。
回答by Rajaneesh
This is working for me In My SQL 5.0
这在我的 SQL 5.0 中对我有用
DELIMITER |
CREATE FUNCTION CamelCase(str VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
DECLARE result VARCHAR(8000);
SET str = CONCAT(' ',str,' ');
SET result = '';
WHILE LENGTH(str) > 1 DO
SET str = SUBSTR(str,INSTR(str,' ')+1,LENGTH(str));
SET result = CONCAT(result,UPPER(LEFT(str,1)), LOWER(SUBSTR(str,2,INSTR(str,' ') - 1)) ) ;
SET str = SUBSTR(str,INSTR(str,' '),LENGTH(str));
END WHILE;
RETURN result;
END
|
DELIMITER ;
回答by Mark
you could do this with concat(), substring(), and length() but I can only see it working for one word. Is there a specific reason why you can't do this in your application's code, instead of mysql?
你可以用 concat()、substring() 和 length() 做到这一点，但我只能看到它对一个词有效。为什么不能在应用程序的代码中而不是在 mysql 中执行此操作，是否有特定原因？
回答by Jonas T
This one works for me.
这个对我有用。
UPDATE `suburbs`
SET title2 = CONCAT_WS(' ',
CONCAT(UPPER(LEFT(SUBSTRING_INDEX(title, ' ',1),1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',1),2))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',2),LENGTH(SUBSTRING_INDEX(title, ' ',1)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',2),3 + LENGTH(SUBSTRING_INDEX(title, ' ',1))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',3),LENGTH(SUBSTRING_INDEX(title, ' ',2)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',3),3 + LENGTH(SUBSTRING_INDEX(title, ' ',2))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',4),LENGTH(SUBSTRING_INDEX(title, ' ',3)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',4),3 + LENGTH(SUBSTRING_INDEX(title, ' ',3))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',5),LENGTH(SUBSTRING_INDEX(title, ' ',4)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',5),3 + LENGTH(SUBSTRING_INDEX(title, ' ',4))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',6),LENGTH(SUBSTRING_INDEX(title, ' ',5)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',6),3 + LENGTH(SUBSTRING_INDEX(title, ' ',5))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',7),LENGTH(SUBSTRING_INDEX(title, ' ',6)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',7),3 + LENGTH(SUBSTRING_INDEX(title, ' ',6))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',8),LENGTH(SUBSTRING_INDEX(title, ' ',7)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',8),3 + LENGTH(SUBSTRING_INDEX(title, ' ',7))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',9),LENGTH(SUBSTRING_INDEX(title, ' ',8)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',9),3 + LENGTH(SUBSTRING_INDEX(title, ' ',8))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',10),LENGTH(SUBSTRING_INDEX(title, ' ',9)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',10),3 + LENGTH(SUBSTRING_INDEX(title, ' ',9))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',11),LENGTH(SUBSTRING_INDEX(title, ' ',10)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',11),3 + LENGTH(SUBSTRING_INDEX(title, ' ',10))))))
WHERE 1
回答by rooskie
The definitive case to look for such a function is in the documentation.
查找此类函数的最终案例在文档中。
Unfortunately, you've got LOWER() and UPPER() functions, but no Title Case. Your best bet would be to declare your own function that splits on spaces, ignores your small words, and does an UPPER on the first character of each remaining word.
不幸的是，您有 LOWER() 和 UPPER() 函数，但没有标题案例。您最好的选择是声明您自己的函数，该函数在空格上拆分，忽略您的小词，并对每个剩余词的第一个字符执行 UPPER。
