如何从自由文本MAT_DESC列提取数量与单位信息？

SQL 解决方案 #

针对支持正则表达式的数据库（如SQL Server 2017+、PostgreSQL、MySQL 8.0+），可通过精准正则匹配覆盖复杂场景：

核心匹配规则 #

需覆盖以下场景：

• 数值：整数、带小数点（如12.34）、带千分位分隔符（如1,234、1,234.56）
• 单位结构：数值/包装单位（如50/Carton）、数值 单位/包装单位（如20 ea/Case）、带空格的单位（如100 pcs / Box）

SQL Server 示例代码 #

-- 提取完整的数量-单位匹配项
SELECT 
    MAT_DESC,
    REGEXP_SUBSTRING(MAT_DESC, 
        N'\b\d{1,3}(,\d{3})*(\.\d+)?\s*(?:\w+\s*)?/\s*\w+\b', 
        1, 1, 'i') AS quantity_unit
FROM your_table;

-- 拆分数值、中间单位、包装单位
SELECT 
    MAT_DESC,
    -- 清理千分位后提取数值
    REPLACE(REGEXP_SUBSTRING(match_val, N'^\d{1,3}(,\d{3})*(\.\d+)?'), ',', '') AS numeric_value,
    -- 提取可选的中间单位（如"ea"）
    TRIM(REGEXP_SUBSTRING(match_val, N'(?<=^\d[^\s/]*\s)\w+(?=\s*/)')) AS inner_unit,
    -- 提取包装单位（如"Carton"）
    TRIM(REGEXP_SUBSTRING(match_val, N'(?<=/\s*)\w+$')) AS package_unit
FROM (
    SELECT 
        MAT_DESC,
        REGEXP_SUBSTRING(MAT_DESC, 
            N'\b\d{1,3}(,\d{3})*(\.\d+)?\s*(?:\w+\s*)?/\s*\w+\b', 
            1, 1, 'i') AS match_val
    FROM your_table
) t
WHERE match_val IS NOT NULL;

其他数据库适配 #

• PostgreSQL：替换为regexp_match或regexp_replace函数，正则语法基本通用
• MySQL：使用REGEXP_SUBSTR，去掉正则前的N前缀即可

Python 解决方案 #

利用Python的re模块，结合环视规则避免误匹配，同时覆盖所有复杂格式：

核心正则表达式 #

import re

# 匹配模式：通过负环视排除数字作为单词一部分的情况，支持所有目标格式
pattern = r'\b(?<!\w)(\d{1,3}(?:,\d{3})*(?:\.\d+)?)\s*([\w\s]*?)/\s*([\w\s]+?)(?!\w)\b'

规则解析：

• \b(?<!\w)：确保数字前无字母/下划线，避免误匹配SKU123/Carton中的123
• \d{1,3}(?:,\d{3})*(?:\.\d+)?：匹配整数、千分位、带小数点的数值
• \s*([\w\s]*?)：匹配可选的中间单位（支持带空格，如each）
• /\s*([\w\s]+?)：匹配包装单位（支持带空格，如Cardboard Box）
• (?!\w)\b：确保匹配项后无字母/下划线，避免误匹配50/CartonXYZ

提取与拆分示例 #

def extract_quantity_unit(desc):
    matches = re.findall(pattern, desc, flags=re.IGNORECASE)
    result = []
    for match in matches:
        # 处理数值：去除千分位，转换为对应类型
        num_str = match[0].replace(',', '')
        numeric_val = float(num_str) if '.' in num_str else int(num_str)
        # 清理单位多余空格
        inner_unit = match[1].strip() if match[1].strip() else None
        package_unit = match[2].strip()
        result.append({
            'numeric_value': numeric_val,
            'inner_unit': inner_unit,
            'package_unit': package_unit
        })
    return result

# 测试用例
test_descs = [
    "50/Carton",
    "20 ea/Case",
    "1,234.56 pcs / Cardboard Box",
    "0.5 kg/Container",
    "Product A - 1000/Bucket"
]
for desc in test_descs:
    print(f"描述: {desc}")
    print(f"提取结果: {extract_quantity_unit(desc)}\n")

误匹配规避技巧 #

• 用单词边界+负环视排除数字作为其他单词组成部分的情况
• 采用非贪婪匹配+?避免过度捕获无关内容
• 通过re.IGNORECASE统一处理单位大小写差异

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