如何在SQL SELECT语句中标准化邮箱地址的姓名首字母大写格式？

由于所有邮箱均为firstname.lastname@xyz.com格式，仅姓名部分大小写不一致，我们可以通过拆分姓名段、分别处理首字母大小写后拼接的方式实现需求，以下是主流数据库的实现方案：

MySQL #

通过嵌套SUBSTRING_INDEX拆分姓名的名和姓，再分别将首字母大写、其余字母小写后拼接：

SELECT
  CONCAT(
    UPPER(SUBSTRING(first_part, 1, 1)),
    LOWER(SUBSTRING(first_part, 2)),
    '.',
    UPPER(SUBSTRING(last_part, 1, 1)),
    LOWER(SUBSTRING(last_part, 2)),
    '@xyz.com'
  ) AS standardized_email
FROM (
  SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', 1), '.', 1) AS first_part,
    SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', 1), '.', -1) AS last_part
  FROM your_table
) AS name_parts;

SQL Server #

使用CHARINDEX定位@符号，PARSENAME拆分姓名的名和姓，结合LEFT/RIGHT处理大小写：

SELECT
  CONCAT(
    UPPER(LEFT(first_part, 1)),
    LOWER(RIGHT(first_part, LEN(first_part) - 1)),
    '.',
    UPPER(LEFT(last_part, 1)),
    LOWER(RIGHT(last_part, LEN(last_part) - 1)),
    '@xyz.com'
  ) AS standardized_email
FROM (
  SELECT
    PARSENAME(SUBSTRING(email, 1, CHARINDEX('@', email) - 1), 2) AS first_part,
    PARSENAME(SUBSTRING(email, 1, CHARINDEX('@', email) - 1), 1) AS last_part
  FROM your_table
) AS name_parts;

PostgreSQL #

PostgreSQL自带INITCAP函数，可直接将字符串中每个单词（以非字母分隔）的首字母大写、其余小写，实现起来最简便：

SELECT
  CONCAT(
    INITCAP(SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1)),
    '@xyz.com'
  ) AS standardized_email
FROM your_table;

说明 #

• 若原邮箱域名可能存在变动（题目中明确为@xyz.com），可将拼接的固定域名替换为SUBSTRING(email FROM POSITION('@' IN email))来保留原域名。
• 所有方案均假设姓名部分严格为firstname.lastname格式，无多余分隔符或特殊字符。

内容的提问来源于stack exchange，提问作者Nick