MySQL查询需求：从products表字符串中提取年份信息

假设你的products表有如下结构和数据：

id name
1 1969-1972 Chevrolet Small Block
2 1971-1975 Chevrolet Small Block Fan Shroud
3 1964 Chevrolet Bumper Kit Complete
4 Chevrolet Clutch And Brake Pedal Assembly

你需要提取名称中的年份信息：

• 如果名称开头是XXXX-XXXX格式，前4位作为start，第6-9位作为end
• 如果只有开头4位数字，start取这4位，end为null
• 如果没有开头数字，start和end都为null

解决方案 #

下面的查询语句可以完美实现这个需求：

SELECT
    id,
    name,
    -- 提取start年份：验证前4位是否为纯数字，是则取值否则返回null
    IF(LEFT(name, 4) REGEXP '^[0-9]{4}$', LEFT(name, 4), NULL) AS start,
    -- 提取end年份：验证第5位是'-'且第6-9位是纯数字，满足则取值否则返回null
    IF(SUBSTRING(name, 5, 1) = '-' AND SUBSTRING(name, 6, 4) REGEXP '^[0-9]{4}$', SUBSTRING(name, 6, 4), NULL) AS end
FROM products;

逻辑拆解 #

• LEFT(name, 4)：截取名称前4个字符，用于判断起始年份是否存在
• REGEXP '^[0-9]{4}$'：用正则表达式严格验证截取的内容是否为4位纯数字
• IF(condition, value_if_true, value_if_false)：根据条件返回对应值，不满足条件时返回null
• SUBSTRING(name, 5, 1)：检查第5个字符是否为年份分隔符-，确保是年份范围格式
• SUBSTRING(name, 6, 4)：当存在合法分隔符时，截取第6到第9位的4位数字作为结束年份

执行该查询后，就能得到你期望的结果：

id name start end
1 1969-1972 Chevrolet Small Block 1969 1972
2 1971-1975 Chevrolet Small Block Fan Shroud 1971 1975
3 1964 Chevrolet Bumper Kit Complete 1964 null
4 Chevrolet Clutch And Brake Pedal Assembly null null

内容的提问来源于stack exchange，提问作者XTRUST.ORG