php - 将字符串中的名称和首字母大写

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

虽然不是很漂亮