MySQL - 将现有表中每个单词的首字母大写

MySQL - Capitalize first letter of each word, in existing table

提问by SirRatty

I have an existing table 'people_table', with a field full_name.

我有一个现有的表“people_table”，有一个字段full_name。

Many records have the 'full_name' field populated with incorrect casing. e.g. 'fred Jones'or 'fred jones'or 'Fred jones'.

许多记录的“full_name”字段填充了错误的大小写。例如'fred Jones'或'fred jones'或'Fred jones'。

I can find these errant entries with:

我可以找到这些错误的条目：

SELECT * FROM people_table WHERE full_name REGEXP BINARY '^[a-z]';

How can I capitalize the first letter of each word found? e.g. 'fred jones'becomes 'Fred Jones'.

如何将找到的每个单词的第一个字母大写？例如'fred jones'变成'Fred Jones'。

回答by Vinicius Pinto

There's no MySQL function to do that, you have to write your own. In the following link there's an implementation:

没有 MySQL 函数可以做到这一点，您必须自己编写。在以下链接中有一个实现：

http://joezack.com/index.php/2008/10/20/mysql-capitalize-function/

http://joezack.com/index.php/2008/10/20/mysql-capitalize-function/

In order to use it, first you need to create the function in the database. You can do this, for example, using MySQL Query Browser (right-click the database name and select Create new Function).

为了使用它，首先需要在数据库中创建函数。例如，您可以使用 MySQL 查询浏览器（右键单击数据库名称并选择创建新函数）来执行此操作。

After creating the function, you can update the values in the table with a query like this:

创建函数后，您可以使用如下查询更新表中的值：

UPDATE users SET name = CAP_FIRST(name);

回答by glerendegui

If you need to run it just one time, and you don't want to create a function, you can do something really-harcoded as:

如果您只需要运行一次，并且不想创建函数，则可以执行一些真正编码为：

UPDATE people_table SET full_name = LOWER(full_name);
UPDATE people_table SET full_name = CONCAT(UPPER(SUBSTR(full_name,1,1)),LOWER(SUBSTR(full_name,2)));
UPDATE people_table SET full_name = REPLACE(full_name,' a',' A');
UPDATE people_table SET full_name = REPLACE(full_name,' b',' B');
UPDATE people_table SET full_name = REPLACE(full_name,' c',' C');
UPDATE people_table SET full_name = REPLACE(full_name,' d',' D');
UPDATE people_table SET full_name = REPLACE(full_name,' e',' E');
UPDATE people_table SET full_name = REPLACE(full_name,' f',' F');
UPDATE people_table SET full_name = REPLACE(full_name,' g',' G');
UPDATE people_table SET full_name = REPLACE(full_name,' h',' H');
UPDATE people_table SET full_name = REPLACE(full_name,' i',' I');
UPDATE people_table SET full_name = REPLACE(full_name,' j',' J');
UPDATE people_table SET full_name = REPLACE(full_name,' k',' K');
UPDATE people_table SET full_name = REPLACE(full_name,' l',' L');
UPDATE people_table SET full_name = REPLACE(full_name,' m',' M');
UPDATE people_table SET full_name = REPLACE(full_name,' n',' N');
UPDATE people_table SET full_name = REPLACE(full_name,' o',' O');
UPDATE people_table SET full_name = REPLACE(full_name,' p',' P');
UPDATE people_table SET full_name = REPLACE(full_name,' q',' Q');
UPDATE people_table SET full_name = REPLACE(full_name,' r',' R');
UPDATE people_table SET full_name = REPLACE(full_name,' s',' S');
UPDATE people_table SET full_name = REPLACE(full_name,' t',' T');
UPDATE people_table SET full_name = REPLACE(full_name,' u',' U');
UPDATE people_table SET full_name = REPLACE(full_name,' v',' V');
UPDATE people_table SET full_name = REPLACE(full_name,' w',' W');
UPDATE people_table SET full_name = REPLACE(full_name,' x',' X');
UPDATE people_table SET full_name = REPLACE(full_name,' y',' Y');
UPDATE people_table SET full_name = REPLACE(full_name,' z',' Z');

回答by Sergio Abreu

If you want to capitalize all words, it will be needed to invoke a custom function.

如果要大写所有单词，则需要调用自定义函数。

-- may help:
-- DROP function if exists capitalize;

DELIMITER $$
CREATE FUNCTION `capitalize`(s varchar(255)) RETURNS varchar(255) 
BEGIN
  declare c int;
  declare x varchar(255);
  declare y varchar(255);
  declare z varchar(255);

  set x = UPPER( SUBSTRING( s, 1, 1));
  set y = SUBSTR( s, 2);
  set c = instr( y, ' ');

  while c > 0
    do
      set z = SUBSTR( y, 1, c);
      set x = CONCAT( x, z);
      set z = UPPER( SUBSTR( y, c+1, 1));
      set x = CONCAT( x, z);
      set y = SUBSTR( y, c+2);
      set c = INSTR( y, ' ');     
  end while;
  set x = CONCAT(x, y);
  return x;
END$$

DELIMITER ;

Now you do this way:

现在你这样做：

 UPDATE mytable SET thefield = capitalize(thefield);

回答by Eric Leroy

Here are two useful functions by Nicholas Thompson. You can set the 3rd variable of UC_DELEMITER to false, and the second to " " for the capitalization of more than one word.

这是 Nicholas Thompson 的两个有用的函数。您可以将 UC_DELEMITER 的第三个变量设置为 false，第二个变量设置为“ ”以表示多个单词的大小写。

UC_FIRSTCapitalize any given string - This function is a clone of the ucfirst function in PHP.

UC_FIRST 将任何给定的字符串大写 - 此函数是 PHP 中 ucfirst 函数的克隆。

DROP FUNCTION IF EXISTS UC_FIRST;
CREATE FUNCTION UC_FIRST(oldWord VARCHAR(255)) RETURNS VARCHAR(255)
RETURN CONCAT(UCASE(SUBSTRING(oldWord, 1, 1)),SUBSTRING(oldWord, 2));

UC_DELIMITERCapitalize with a delimiter in between words

UC_DELIMITER在单词之间用分隔符大写

DROP FUNCTION IF EXISTS UC_DELIMITER;
DELIMITER //
CREATE FUNCTION UC_DELIMITER(
   oldName VARCHAR(255), delim VARCHAR(1), trimSpaces BOOL
) 
  RETURNS VARCHAR(255)
BEGIN
SET @oldString := oldName;
SET @newString := "";

tokenLoop: LOOP
    IF trimSpaces THEN SET @oldString := TRIM(BOTH " " FROM @oldString);          END IF;

SET @splitPoint := LOCATE(delim, @oldString);

IF @splitPoint = 0 THEN
  SET @newString := CONCAT(@newString, UC_FIRST(@oldString));
  LEAVE tokenLoop;
END IF;

   SET @newString := CONCAT(@newString, UC_FIRST(SUBSTRING(@oldString, 1, @splitPoint)));
   SET @oldString := SUBSTRING(@oldString, @splitPoint+1);
END LOOP tokenLoop;

RETURN @newString;
END//
DELIMITER ;

Examples:

例子：

SELECT UC_DELIMITER('eric-leroy','-',TRUE);
Eric-Leroy  

Function's Webpage

函数的网页

回答by Steve Robb - Q2Q IT LTD

I tried the code from above but had syntax errors on function, so could not create it. Wrote this for latest version of MySQL if it helps anyone

我尝试了上面的代码，但在函数上有语法错误，所以无法创建它。如果对任何人有帮助，请为最新版本的 MySQL 编写此内容

CREATE FUNCTION  `CAP_FIRST`(input VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
    DETERMINISTIC
BEGIN
    DECLARE len INT;
    DECLARE i INT;
    DECLARE charnum INT;
    declare SortedName varchar(255);

    SET len   = CHAR_LENGTH(input);
    SET input = LOWER(input);
    SET i = 1;
    set charnum = 1;
    set SortedName = '';


    WHILE (i <= len) DO
        if charnum = 1 then
            set SortedName = concat(SortedName,upper(mid(input,i,1)));
            set charnum = charnum + 1;
        else
            if mid(input,i,1) = ' ' then
                set SortedName = concat(SortedName,' ');
                set charnum = 1;
            else
                set SortedName = concat(SortedName,mid(input,i,1));
                set charnum = charnum + 1;
            end if;

        end if;


        SET i = i + 1;
    END WHILE;

    RETURN SortedName;
END

回答by Vijay Chauhan

DELIMITER $$
CREATE FUNCTION `capitalize`(s varchar(255)) RETURNS varchar(255) 
BEGIN
  declare c int;
  declare x varchar(255);
  declare y varchar(255);
  declare z varchar(255);

  set x = UPPER( SUBSTRING( s, 1, 1));
  set y = lower(SUBSTR( s, 2));
  set c = instr( y, ' ');

  while c > 0
    do
      set z = SUBSTR( y, 1, c);
      set x = CONCAT( x, z);
      set z = UPPER( SUBSTR( y, c+1, 1));
      set x = CONCAT( x, z);
      set y = SUBSTR( y, c+2);
      set c = INSTR( y, ' ');     
  end while;
  set x = CONCAT(x, y);
  return x;
END$$

DELIMITER ;

Create above function to set First character to capital of each words

回答by Thomas

Simply :

简单地 ：

SELECT
CONCAT(UCASE(LEFT(firstname, 1)), LCASE(SUBSTRING(firstname, 2))) as firstname
FROM PEOPLE

回答by Hitarth_N

No need for creating a function if it is a one timer. The below works just fine:

如果是单定时器，则无需创建函数。以下工作正常：

-- Capitalize first letter of each word in r.name field
SELECT TRIM(CONCAT(
   CONCAT(UPPER(SUBSTRING(cname1,1,1)),SUBSTRING(cname1,2)) , " ",
   CONCAT(UPPER(SUBSTRING(cname2,1,1)),SUBSTRING(cname2,2)) , " ",
   CONCAT(UPPER(SUBSTRING(cname3,1,1)),SUBSTRING(cname3,2))))
FROM (
   SELECT
     @num_spaces := 1 + LENGTH(c_name) - LENGTH(REPLACE(c_name, ' ', '')) AS 
     num_spaces,
     SUBSTRING_INDEX(CONVERT(c_name,CHAR), ' ', 1) AS cname1,
     IF(@num_spaces > 1, SUBSTRING_INDEX(SUBSTRING_INDEX(c_name, ' ', 2), ' ', -1), '') AS cname2,
     IF(@num_spaces > 2, SUBSTRING_INDEX(SUBSTRING_INDEX(c_name, ' ', 3), ' ', -1), '') AS cname3
     FROM (SELECT (CASE 
        WHEN UPPER(r.name)COLLATE latin1_general_cs =r.name THEN LOWER(TRIM(r.name))
        ELSE TRIM(r.name)
        END) AS c_name,r.name
        FROM table r) cr) ncr;

NOTE:The IF clause should be equal to or more than the value of @num_spaces. The current sql will take care of at max 3 words. You may add more if required.

注意：IF 子句应该等于或大于@num_spaces 的值。当前的 sql 将处理最多 3 个单词。如果需要，您可以添加更多。

回答by Matt C.

The Properfunction in Excel (or google sheets does exactly what you want.

Excel 中的Proper函数（或谷歌表格完全符合您的要求。

So, export your mysql table as CSV and into Excel (or google sheets). Then use the = Proper(*text_to_capitalize*)to capitalize the first letter of each word.

因此，将您的 mysql 表导出为 CSV 并导入 Excel（或 Google 表格）。然后使用 将= Proper(*text_to_capitalize*)每个单词的第一个字母大写。

Then just export that excel sheet as CSV back into your database.

然后只需将该 Excel 表作为 CSV 导出回您的数据库。

回答by Matt C.

If you are using PHP then...

如果您使用的是 PHP，那么...

try{
  $con = new PDO("mysql:host=localhost;dbname=dbasename", "root", "");
}
catch(PDOException $e){
  echo "error" . $e-getMessage();
}

$select = $con->prepare("SELECT * FROM table");
$select->setFetchMode(PDO::FETCH_ASSOC);
$select->execute();

while($data=$select->fetch()) {

  $id = $data['id'];
  $column = $data['column'];
  $column = ucwords(strtolower($column)); // Capitalize each word

  $update = $con->prepare("UPDATE table SET column=:column WHERE id='$id'");
  $update->bindParam(':column', $column);
  $update->execute();
}