首字母大写。MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4263272/
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
Capitalize first letter. MySQL
提问by Chin
Does any one know the equivalent to this TSQL in MySQL parlance?
有没有人知道 MySQL 中这个 TSQL 的等价物？
I am trying to capitalize the first letter of each entry.
我试图将每个条目的第一个字母大写。
UPDATE tb_Company SET CompanyIndustry = UPPER(LEFT(CompanyIndustry, 1))
+ SUBSTRING(CompanyIndustry, 2, LEN(CompanyIndustry))
回答by Vincent Savard
It's almost the same, you just have to change to use the CONCAT() function instead of the + operator :
几乎相同，您只需更改为使用 CONCAT() 函数而不是 + 运算符：
UPDATE tb_Company
SET CompanyIndustry = CONCAT(UCASE(LEFT(CompanyIndustry, 1)),
SUBSTRING(CompanyIndustry, 2));
This would turn helloto Hello, wOrLdto WOrLd, BLABLAto BLABLA, etc. If you want to upper-case the first letter and lower-case the other, you just have to use LCASE function :
这会变成hello对Hello，wOrLd对WOrLd，BLABLA对BLABLA等，如果你想为大写首字母和小写其他的，你只需要使用LCASE函数：
UPDATE tb_Company
SET CompanyIndustry = CONCAT(UCASE(LEFT(CompanyIndustry, 1)),
LCASE(SUBSTRING(CompanyIndustry, 2)));
Note that UPPER and UCASE do the same thing.
注意 UPPER 和 UCASE 做同样的事情。
回答by Martin Sansone - MiOEE
Vincents excellent answer for Uppercase FirstLetter works great for the first letter onlycapitalization of an entire column string..
文森大写出色答卷第一字母的第一个字母的伟大工程，只有一整列的字符串大小写..
BUT what if you want to Uppercase the First Letter of EVERY wordin the strings of a table column?
但是，如果您想将表列字符串中每个单词的首字母大写怎么办？
eg: "Abbeville High School"
例如：“阿比维尔高中”
I hadn't found an answer to this in Stackoverflow. I had to cobble together a few answers I found in Google to provide a solid solution to the above example. Its not a native function but a user created function which MySQL version 5+ allows.
我在 Stackoverflow 中没有找到答案。我不得不拼凑一些我在谷歌找到的答案，为上面的例子提供一个可靠的解决方案。它不是本机函数，而是 MySQL 5+ 版允许的用户创建函数。
If you have Super/Admin user status on MySQL or have a local mysql installation on your own computer you can create a FUNCTION (like a stored procedure) which sits in your database and can be used in all future SQL query on any part of the db.
如果您在 MySQL 上拥有超级/管理员用户状态，或者在您自己的计算机上安装了本地 mysql，您可以创建一个位于您的数据库中的 FUNCTION（如存储过程），并且可以在以后的任何部分的 SQL 查询中使用。 D b。
The function I created allows me to use this new function I called "UC_Words" just like the built in native functions of MySQL so that I can update a complete column like this:
我创建的函数允许我使用这个我称为“UC_Words”的新函数，就像 MySQL 的内置本机函数一样，以便我可以像这样更新完整的列：
UPDATE Table_name
SET column_name = UC_Words(column_name)
To insert the function code, I changed the MySQL standard delimiter(;) whilst creating the function, and then reset it back to normal after the function creation script. I also personally wanted the output to be in UTF8 CHARSET too.
为了插入函数代码，我在创建函数时更改了 MySQL 标准分隔符(;)，然后在函数创建脚本后将其重置为正常。我个人也希望输出也采用 UTF8 CHARSET。
Function creation =
函数创建 =
DELIMITER ||
CREATE FUNCTION `UC_Words`( str VARCHAR(255) ) RETURNS VARCHAR(255) CHARSET utf8 DETERMINISTIC
BEGIN
DECLARE c CHAR(1);
DECLARE s VARCHAR(255);
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 ;
This works a treat outputting Uppercase first letters on multiple words within a string.
这适用于在字符串中的多个单词上输出大写首字母。
Assuming your MySQL login username has sufficient privileges - if not, and you cant set up a temporary DB on your personal machine to convert your tables, then ask your shared hosting provider if they will set this function for you.
假设您的 MySQL 登录用户名具有足够的权限 - 如果没有，并且您无法在您的个人计算机上设置临时数据库来转换您的表，那么请询问您的共享主机提供商是否会为您设置此功能。
回答by Wouter Dorgelo
You can use a combination of UCASE(), MID()and CONCAT():
您可以使用UCASE(),MID()和的组合CONCAT()：
SELECT CONCAT(UCASE(MID(name,1,1)),MID(name,2)) AS name FROM names;
回答by Root
mysql> SELECT schedule_type AS Schedule FROM ad_campaign limit 1;
+----------+
| Schedule |
+----------+
| ENDDATE |
+----------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT(UCASE(MID(schedule_type,1,1)),LCASE(MID(schedule_type,2))) AS Schedule FROM ad_campaign limit 1;
+----------+
| Schedule |
+----------+
| Enddate |
+----------+
1 row in set (0.00 sec)
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_mid
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_mid
回答by shantanuo
http://forge.mysql.com/tools/tool.php?id=201
http://forge.mysql.com/tools/tool.php?id=201
If there are more than 1 word in the column, then this will not work as shown below. The UDF mentioned above may help in such case.
如果该列中的单词超过 1 个，则如下所示这将不起作用。在这种情况下，上面提到的 UDF 可能会有所帮助。
mysql> select * from names;
+--------------+
| name |
+--------------+
| john abraham |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT(UCASE(MID(name,1,1)),MID(name,2)) AS name FROM names;
+--------------+
| name |
+--------------+
| John abraham |
+--------------+
1 row in set (0.00 sec)
Or maybe this one will help...
或者，也许这个会有所帮助...
回答by Abhinav Sahu
This is working nicely.
这工作得很好。
UPDATE state SET name = CONCAT(UCASE(LEFT(name, 1)), LCASE(SUBSTRING(name, 2)));
回答by Florin
CREATE A FUNCTION:
创建函数：
CREATE DEFINER=`root`@`localhost` FUNCTION `UC_FIRST`(`oldWord` VARCHAR(255))
RETURNS varchar(255) CHARSET utf8
RETURN CONCAT( UCASE( LEFT(oldWord, 1)), LCASE(SUBSTRING(oldWord, 2)))
USE THE FUNCTION
使用功能
UPDATE tbl_name SET col_name = UC_FIRST(col_name);
回答by Jasdeep Singh
UPDATE tb_Company SET CompanyIndustry = UCASE(LEFT(CompanyIndustry, 1)) +
SUBSTRING(CompanyIndustry, 2, LEN(CompanyIndustry))
回答by Jahir islam
If anyone try to capitalize the every word separate by space...
如果有人试图大写由空格分隔的每个单词......
CREATE FUNCTION response(name VARCHAR(40)) RETURNS VARCHAR(200) DETERMINISTIC
BEGIN
set @m='';
set @c=0;
set @l=1;
while @c <= char_length(name)-char_length(replace(name,' ','')) do
set @c = @c+1;
set @p = SUBSTRING_INDEX(name,' ',@c);
set @k = substring(name,@l,char_length(@p)-@l+1);
set @l = char_length(@k)+2;
set @m = concat(@m,ucase(left(@k,1)),lcase(substring(@k,2)),' ');
end while;
return trim(@m);
END;
CREATE PROCEDURE updateNames()
BEGIN
SELECT response(name) AS name FROM names;
END;
Result
结果
+--------------+
| name |
+--------------+
| Abdul Karim |
+--------------+
回答by Aamir Khan
select CONCAT(UCASE(LEFT('CHRIS', 1)),SUBSTRING(lower('CHRIS'),2));
Above statement can be used for first letter CAPS and rest as lower case.
以上语句可用于首字母大写，其余为小写。
