字符串中的姓名和首字母大写

Question

我有许多记录以不同的组合存储名称，例如-

first_name  | last_name
Joe         | BLOGGS
Jane Louise | SMITH
JB          | Smith
A B         | Jones

我需要用正确的大小写显示这些名字-

full_name
Joe Bloggs
Jane Louise Smith
JB Smith
AB Jones

我知道PHP有各种内置的函数，可以将字符串转换为大写、小写、首字母大写等，但我找不到任何可以正确处理首字母的函数。

有没有人有能做到这一点的脚本？或者，如果这在MySQL中是可能的，那么这将是一个选择。

Answer 1

SELECT CONCAT(UPPER(SUBSTRING(firstName, 1, 1)), LOWER(SUBSTRING(firstName FROM 2))) AS properFirstName

来自MYSQL Website

所以对于你的问题：

SELECT CASE WHEN LENGTH(first_name) = 2 OR (LENGTH(first_name) = 3 AND first_name LIKE '% %')  -- check if Initials as first name
            THEN CONCAT(UPPER(REPLACE(first_name, " ", "")),                                   -- initials in UPPER, no spaces
                        " ", 
                        UPPER(SUBSTRING(last_name, 1, 1)),                                    -- First letter last Name
                        LOWER(SUBSTRING(last_name FROM 2)))                                   -- Rest last Name
            ELSE (CASE WHEN first_name LIKE '% %'                                              -- check if first_name contains space
                       THEN CONCAT(UPPER(SUBSTRING(first_name, 1, 1)),
                                   LOWER(SUBSTRING(first_name, 2, INSTR(first_name, " ")-2)),
                                   " ",
                                   UPPER(SUBSTRING(first_name, INSTR(first_name, " ")+1, 1)),
                                   LOWER(SUBSTRING(first_name FROM INSTR(first_name, " ")+2)),  
                                   " ",                                                         -- Space between first and last Name
                                   UPPER(SUBSTRING(last_name, 1, 1)),                           -- First letter last Name
                                   LOWER(SUBSTRING(last_name FROM 2)))                          -- Rest last Name
                       ELSE CONCAT(UPPER(SUBSTRING(first_name, 1, 1)),                          -- First letter first Name
                                   LOWER(SUBSTRING(first_name FROM 2)),                         -- Rest first Name
                                   " ",                                                         -- Space between first and last Name
                                   UPPER(SUBSTRING(last_name, 1, 1)),                           -- First letter last Name
                                   LOWER(SUBSTRING(last_name FROM 2))) END)END AS properName    -- Rest last Name

虽然不是很漂亮