SQL如何校验邮箱首字符为's'并解决CASE语句不生效问题

原脚本错误原因 #

第二个CASE语句逻辑完全错误：原写法是将完整邮箱地址和单个首字符做不等值判断，无法实现「校验邮箱首字符是否为's'」的需求，同时重复的字符串截取逻辑也增加了代码冗余。

优化后完整脚本 #

SELECT
    p.personID,
    p.studentNumber,
    c.email,
    -- 仅当邮箱格式合法时提取邮箱中的学号
    CASE WHEN CHARINDEX('@', c.email) > 0 
         THEN SUBSTRING(c.email, 2, CHARINDEX('@', c.email) - 2) 
         ELSE NULL END AS StudentNumberFromEmail,
    LEFT(c.email, 1) AS SfromEmail,
    -- 校验邮箱中学号与系统学号是否匹配
    CASE 
        WHEN c.email IS NULL OR LTRIM(c.email) = '' THEN 'email missing'
        WHEN CHARINDEX('@', c.email) = 0 THEN 'invalid format'
        WHEN p.studentNumber <> SUBSTRING(c.email, 2, CHARINDEX('@', c.email) - 2) THEN 'mismatched'
        ELSE 'matched' 
    END AS studentno_check_status,
    -- 生成统一修正后的邮箱
    CONCAT('s', p.studentNumber, '@myemail.org') AS corrected_email,
    -- 校验邮箱首字符是否为's'
    CASE 
        WHEN c.email IS NULL OR LTRIM(c.email) = '' THEN 'email missing'
        WHEN LEFT(c.email, 1) <> 's' THEN 'mismatched'
        ELSE 'matched' 
    END AS first_char_check_status
FROM Person p
JOIN Enrollment e 
    ON e.personID = p.personID
    AND e.endDate IS NULL 
    AND e.serviceType = 'P' 
JOIN calendar cal ON cal.calendarID = e.calendarID 
JOIN SchoolYear sy 
    ON sy.endYear = e.endYear 
    AND sy.active = 1
JOIN Contact c ON c.personID = p.personID

核心优化点 #

• 首字符校验直接使用LEFT()函数取邮箱第一个字符和's'做对比，逻辑简洁易懂
• 新增邮箱格式合法性判断，避免邮箱不含@时字符串截取报错
• 空邮箱、空白字符邮箱统一归为email missing状态，校验逻辑更严谨
• 相同截取逻辑可以进一步封装到子查询/CTE中，扩展更多校验规则时可降低重复代码量

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