如何从非标准化产品描述字符串中提取宽高数值？

问题背景 #

需要将产品表Description字段中混杂的宽高信息，拆分到独立的数值列中，避免每次查询都处理长字符串。描述格式存在多种变体：

• 常见格式：W" X H"
• 变体格式：W"XH"、-W"xH"-等
• 额外干扰：描述中可能存在用于圆角、长度的第三个双引号

固定规则 #

• 宽高均为整数
• 宽高后直接跟随双引号"
• 宽高之间必含一个不区分大小写的X

变量情况 #

• 尺寸前后的空格、周边字符无固定格式
• 描述中双引号数量可能超过2个
• 宽高可为1/2/3位数字（如1" X 1"、10" X 1"、100" X 1"）

原有SQL的问题 #

原有SQL通过多层CHARINDEX和LEFT/RIGHT截取字符，在处理3位数宽度（如100"x80"）时会出现截断（结果为00），原因是逻辑依赖字符位置的反向计算，未准确定位X的分隔作用，对数字长度的兼容性不足。

通用解决方案 #

以X（不区分大小写）为核心分隔符，分别提取其前后的数字序列（后跟"），以下分两种常见SQL环境给出实现：

方案1：SQL Server环境 #

SELECT
    -- 提取宽度：匹配X之前最后一组带双引号的数字
    SUBSTRING(
        Description,
        PATINDEX('%[0-9]"%[^X]*[Xx]%', Description),
        CHARINDEX('"', Description, PATINDEX('%[0-9]"%[^X]*[Xx]%', Description)) 
        - PATINDEX('%[0-9]"%[^X]*[Xx]%', Description)
    ) AS Width,
    -- 提取高度：匹配X之后第一组带双引号的数字
    SUBSTRING(
        Description,
        PATINDEX('%[Xx]%[0-9]"', Description),
        CHARINDEX('"', Description, PATINDEX('%[Xx]%[0-9]"', Description)) 
        - PATINDEX('%[Xx]%[0-9]"', Description)
    ) AS Height,
    Description
FROM HQMT
-- 过滤符合宽高格式的记录
WHERE PATINDEX('%[0-9]"[^X]*[Xx][^X]*[0-9]"', Description) > 0

方案2：MySQL环境（支持正则函数） #

SELECT
    -- 提取X前、双引号前的数字
    REGEXP_SUBSTR(Description, '[0-9]+(?="[^X]*[Xx])') AS Width,
    -- 提取X后、双引号前的数字
    REGEXP_SUBSTR(Description, '(?<=[Xx][^"]*)[0-9]+(?=")') AS Height,
    Description
FROM HQMT
-- 过滤符合格式的记录
WHERE Description REGEXP '[0-9]"[^X]*[Xx][^X]*[0-9]"';

方案优势 #

• 兼容1-3位数字的宽高，无需调整逻辑适配数字长度
• 自动忽略宽高前后的空格、无关字符
• 不受描述中多余双引号的干扰
• 不区分X的大小写，覆盖所有变体格式

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