将现有表格中每个单词的首字母大写
我有一个现有表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);
相关文章