mysql - 将现有表的每个单词大写

Question

我有一个名为“艺术家”的表。 “艺术家”中有一个名为“艺术家”的栏目。

Artist
firstname lastname
Firstname lastname
Firstname Lastname
firstname

我尝试了对此 Post 的查询

我想在“艺术家”表中将每个艺术家名字和姓氏的首字母大写。期待:

Artist
Firstname Lastname
Firstname Lastname
Firstname Lastname
Firstname

我的一些艺术家没有名字，查询在开头添加了一个额外的字符:

Answer 1

解决此问题的一种方法是使用 CASE 表达式，当 Artist 有两个名字时，从另一个问题中选择公式，并且仅将第一个字母大写当只有一个名字时:

SELECT 
    CASE WHEN Locate(' ', Artist) = 0 THEN 
         -- firstname only
         CONCAT(UPPER(SUBSTRING(Artist, 1, 1)), 
                LOWER(SUBSTRING(Artist, 2)))
    ELSE
         -- firstname and lastname
         CONCAT(UPPER(SUBSTRING(Artist, 1, 1)),
                LOWER(SUBSTRING(Artist, 2, Locate(' ', Artist)-1)),
                UPPER(SUBSTRING(Artist, Locate(' ', Artist)+1,1)),
                LOWER(SUBSTRING(Artist, Locate(' ', Artist)+2)))
    END AS Artist
FROM Artists

输出:

Artist
Firstname Lastname
Firstname Lastname
Firstname Lastname
Firstname Lastname
Firstname
Firstname

Demo on dbfiddle