MySQL-在现有表格中将每个单词的首字母大写 [英] MySQL - Capitalize first letter of each word, in existing table

问题描述

我有一个现有的表'people_table'，其中有一个字段full_name.

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

许多记录的"full_name"字段填充了不正确的大小写.例如'fred Jones'或'fred jones'或'Fred jones'.

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

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

I can find these errant entries with:

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

如何将找到的每个单词的首字母大写?例如'fred jones'变为'Fred Jones'.

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

推荐答案

没有MySQL函数可以执行此操作，您必须编写自己的函数.在下面的链接中有一个实现:

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

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

要使用它，首先需要在数据库中创建函数.例如，您可以使用MySQL查询浏览器(右键单击数据库名称并选择创建新功能")来执行此操作.

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

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

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

UPDATE users SET name = CAP_FIRST(name);

这篇关于MySQL-在现有表格中将每个单词的首字母大写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！