在现有表中将每个单词的第一个字母大写

Question

我有一个现有的表“people_table”，其中包含一个字段full_name。

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

我可以通过以下方式找到这些错误条目：

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

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

Answer 1

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

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

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

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

UPDATE users SET name = CAP_FIRST(name);

Answer 2

如果您只需要运行一次，并且不想创建函数，则可以执行一些真正的硬编码操作，如下所示：

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');

Answer 3

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

-- 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 ;

现在你这样做：

 UPDATE mytable SET thefield = capitalize(thefield);

Answer 4

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

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_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 ;

示例：

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

函数的网页

Answer 5

我尝试了上面的代码，但函数有语法错误，因此无法创建它。如果对任何人有帮助的话，这是为最新版本的 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

Answer 6

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

Answer 7

简单地 ：

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

Answer 8

如果它是一个定时器，则无需创建函数。下面的代码工作得很好：

-- 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;

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

Answer 9

首先创建一个函数

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));

，然后使用这个查询

UPDATE mytable SET thefield = UC_FIRST(thefield);

Answer 10

Eric Leroy 的答案是这里最有趣的答案，但它忽略了两件事：

• UC_FIRST 函数中的小写：如果您想将单词的第一个字母大写，则意味着您也希望其他字母小写。它适用于完整的小写条目，但如果在条目中出现混合大小写，例如“wOrD”，那么当您想要“Word”时，您会得到“WOrD”。
• 根据 mysql 版本和配置的不同，您可能需要在函数定义中使用确定性关键字。

以下是创建函数的更新代码：

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

DROP FUNCTION IF EXISTS UC_DELIMITER;
DELIMITER //
CREATE FUNCTION UC_DELIMITER(
   oldName VARCHAR(255), delim VARCHAR(1), trimSpaces BOOL
) 
  RETURNS VARCHAR(255) DETERMINISTIC
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 ;

通过以下请求：

SELECT UC_DELIMITER('ThIs IS an ExAMplE oF MIxeD CaseS sTRing',' ',TRUE);

您将得到：

This Is An Example Of Mixed Cases String

Answer 11

这可以通过使用 LOWER、UPPER 和 SUBSTRING 函数来完成，请参见下面的示例查询

Select CONCAT(UPPER(SUBSTRING(name,1,1)),LOWER(SUBSTRING(name,2))) AS Name from table;

在 UPDATE 查询中，这将是

UPDATE table1 SET name = CONCAT(UPPER(SUBSTRING(name,1,1)),LOWER(SUBSTRING(name,2))) WHERE 1

Answer 12

如果您想避免编写自己的存储函数，可以使用 JSON_TABLE 将文本“分解”为多行（每个单词一个），然后处理每个单词并将它们连接在一起。

-- temp table of data to demonstrate with
CREATE TEMPORARY TABLE tmp_names AS
VALUES ROW(1, "ted theodore LOGAN")
     , ROW(2, "BiLL S preston esquire")
     , ROW(3, "rufus")
;

-- in the update we can't open the temp table twice so duplicate
-- in real you'd just use your original table both times
CREATE TEMPORARY TABLE tmp_names2 AS SELECT * FROM tmp_names; 

-- for understanding: see what we've turned the table into
SELECT *
FROM tmp_names AS t
INNER JOIN JSON_TABLE(
    CONCAT('["', REPLACE(t.column_1, ' ', '","'), '"]')
    , "$[*]" COLUMNS (name VARCHAR(100) PATH "$")
    ) AS s
;

-- update the data by running all of this
-- or just run the subquery part to see how the GROUP_CONCAT turns out
UPDATE tmp_names AS t
INNER JOIN (
-- subquery start
  SELECT t.column_0, t.column_1
       , GROUP_CONCAT(
           CONCAT(
             UPPER(SUBSTR(s.name, 1, 1))
             , LOWER(SUBSTR(s.name, 2))
           )
           SEPARATOR ' '
       ) AS title_cased
  FROM tmp_names2 AS t
  INNER JOIN JSON_TABLE(
      CONCAT('["', REPLACE(t.column_1, ' ', '","'), '"]')
      , "$[*]" COLUMNS (name VARCHAR(100) PATH "$")
      ) AS s
  GROUP BY 1, 2
-- subquery end
) AS j
  ON j.column_0 = t.column_0
SET t.column_1 = j.title_cased
;

-- see the beautiful result
SELECT * FROM tmp_names;

Answer 13

仅使用 REGEXP_REPLACE 解决方案

WITH d AS (
    SELECT 'Lorem Ipsum is simply dummy text of the printing and typesetting industry.' s
)
, d2 AS (
    SELECT *
    , CONCAT(LOWER(s), UPPER(s)) s_l_u
    , CONCAT('(?<=[[:space:]]|^)[a-z](?=.{', (CHAR_LENGTH(s) - 1), '}([A-Z]))') reg_exp
    , CHAR_LENGTH(s) len
    FROM d
)
SELECT version() ver, s, SUBSTR(REGEXP_REPLACE(s_l_u, reg_exp, '$1'), 1, len) cap
FROM d2

结果：

+------+--------------------------------------------------------------------------+--------------------------------------------------------------------------+
|ver   |s                                                                         |cap                                                                       |
+------+--------------------------------------------------------------------------+--------------------------------------------------------------------------+
|8.0.35|Lorem Ipsum is simply dummy text of the printing and typesetting industry.|Lorem Ipsum Is Simply Dummy Text Of The Printing And Typesetting Industry.|
+------+--------------------------------------------------------------------------+--------------------------------------------------------------------------+

Answer 14

在 phpmyadmin 上，运行此 UPDATE table_name SET Column_Name = LOWER(Column_Name)
然后在显示数据库表数据的html页面中使用css text-transform: Capitalize;

Answer 15

Excel（或谷歌表格）中的 Proper 函数完全可以满足您的需求。

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

然后将该 Excel 工作表以 CSV 格式导出回数据库。

Answer 16

如果您使用 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();
}