如何对 mysql 列进行“适当的大小写”格式设置？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6181937/
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
How to do a 'Proper case' formatting of a mysql column?
提问by Shyam Natraj Kanagasabapathy
Is it possible in mysql to format a column in Proper Case?
在 mysql 中是否可以在适当的情况下格式化列？
Example: Proper("ABSALOM") = "Absalom"
例子： Proper("ABSALOM") = "Absalom"
I have searched a lot and I think MySQL doesn't have any inbuilt function to do this. Is it possible to do this any other way in MySQL?
我已经搜索了很多，我认为 MySQL 没有任何内置功能可以做到这一点。是否可以在 MySQL 中以任何其他方式执行此操作？
回答by Dave Maple
You can combine CONCAT and SUBSTRING:
您可以组合 CONCAT 和 SUBSTRING：
CONCAT(UCASE(SUBSTRING(`fieldName`, 1, 1)), LOWER(SUBSTRING(`fieldName`, 2)))
回答by Pascal
You would think that the world's most popular open source database, as MySQL like to call itself, would have a function for making items title case (where the first letter of every word is capitalized). Sadly it doesn't.
你会认为世界上最流行的开源数据库，就像 MySQL 喜欢自称的那样，会有一个使项目标题大小写的功能（每个单词的第一个字母大写）。遗憾的是它没有。
This is the best solution i found Just create a stored procedure / function that will do the trick
这是我发现的最好的解决方案只需创建一个可以解决问题的存储过程/函数
mysql>
DROP FUNCTION IF EXISTS proper;
SET GLOBAL log_bin_trust_function_creators=TRUE;
DELIMITER |
CREATE FUNCTION proper( str VARCHAR(128) )
RETURNS VARCHAR(128)
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;
RETURN s;
END;
|
DELIMITER ;
then
然后
update table set col = proper(col)
or
或者
select proper( col ) as properCOl
from table
Tada Your are welcome
多田 不客气
回答by michelek
@Pascal's solution works on latin characters. Any meddling with different collations messes things up.
@Pascal 的解决方案适用于拉丁字符。任何对不同排序规则的干预都会把事情搞砸。
I think what @Pascal really meant goes more like this:
我认为@Pascal 真正的意思更像是这样：
DELIMITER |
CREATE or replace FUNCTION func_proper( str VARCHAR(255) )
RETURNS VARCHAR(255)
BEGIN
DECLARE chr VARCHAR(1);
DECLARE lStr VARCHAR(255);
DECLARE oStr VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 1;
DECLARE bool INT DEFAULT 1;
DECLARE punct CHAR(17) DEFAULT ' ()[]{},.-_!@;:?/';
WHILE i <= LENGTH( str ) DO
BEGIN
SET chr = SUBSTRING( str, i, 1 );
IF LOCATE( chr, punct ) > 0 THEN
BEGIN
SET bool = 1;
SET oStr = concat(oStr, chr);
END;
ELSEIF bool=1 THEN
BEGIN
SET oStr = concat(oStr, UCASE(chr));
SET bool = 0;
END;
ELSE
BEGIN
SET oStr = concat(oStr, LCASE(chr));
END;
END IF;
SET i = i+1;
END;
END WHILE;
RETURN oStr;
END;
|
DELIMITER ;
回答by thanoskam
-- set upper case the first char of every word in a string
DROP FUNCTION IF EXISTS PROPER;
DELIMITER $$
CREATE FUNCTION PROPER(inputStr varchar(1500))
RETURNS VARCHAR(1500)
DETERMINISTIC
BEGIN
DECLARE x, y, result VARCHAR(1500);
SET result = '';
SET x = '';
SET y = LOWER(TRIM(inputStr));
WHILE CHAR_LENGTH(y) > 0 DO
-- get next word
SET x = SUBSTRING_INDEX(y, ' ', 1);
-- set upper case the first char
SET x = CONCAT(UPPER(SUBSTRING(x, 1, 1)), SUBSTRING(x, 2));
-- the final s (greek language)
IF RIGHT(x,1) = 'σ' THEN
SET x = CONCAT(left(x, CHAR_LENGTH(x) - 1),'?');
END IF;
-- add word to result
SET result = CONCAT(result, ' ', x);
-- prepare y for next loop
SET y = TRIM(SUBSTRING(y, CHAR_LENGTH(x) + 1));
END WHILE;
RETURN (TRIM(result));
END$$
DELIMITER;
