MS SQL中从随机字符串提取Number x Number格式尺寸的方案求助

核心问题分析 #

之前的逻辑未覆盖**带空格的混合分数（如1 3/4）**这类格式，导致匹配失败。需要调整匹配规则，同时兼容纯整数、纯分数、混合分数三种数字格式，还要忽略x/X前后的空格。

可行方案：使用XQuery正则匹配 #

以下是修改后的测试代码，兼容所有目标格式：

WITH TestData AS (
    SELECT '1 3/4 X 6' AS InputString UNION ALL
    SELECT '2x3/4' UNION ALL
    SELECT '5/8 x 10' UNION ALL
    SELECT '3 X 7 1/2' UNION ALL
    SELECT 'Random text 4 1/4 x 9 more text' UNION ALL
    SELECT 'No size here' UNION ALL
    SELECT '10x12'
)
SELECT 
    InputString,
    -- 提取匹配的尺寸字符串
    CAST('<root><s>' + REPLACE(InputString, ' ', '</s><s>') + '</s></root>' AS XML)
        .query('
            for $match in fn:tokenize(., "\s*[xX]\s*")
            let $num1 := $match[1]
            let $num2 := $match[2]
            where fn:matches($num1, "^(\d+\s*\d+\/\d+|\d+\/\d+|\d+)$")
              and fn:matches($num2, "^(\d+\s*\d+\/\d+|\d+\/\d+|\d+)$")
            return concat($num1, " x ", $num2)
        ').value('.', 'NVARCHAR(100)') AS ExtractedSize
FROM TestData;

正则规则详解 #

匹配单个数字部分的正则^(\d+\s*\d+\/\d+|\d+\/\d+|\d+)$包含三种情况：

• \d+\s*\d+\/\d+：匹配带整数的混合分数（如1 3/4，允许整数和分数间有空格）
• \d+\/\d+：匹配纯分数（如3/4）
• \d+：匹配纯整数（如6）

中间的\s*[xX]\s*匹配大小写的x，并忽略前后任意数量的空格。

备选方案：使用PATINDEX分步提取 #

如果不想用XQuery，也可以用PATINDEX结合字符串操作分步提取：

WITH TestData AS (
    SELECT '1 3/4 X 6' AS InputString UNION ALL
    SELECT '2x3/4' UNION ALL
    SELECT '5/8 x 10' UNION ALL
    SELECT '3 X 7 1/2' UNION ALL
    SELECT 'Random text 4 1/4 x 9 more text' UNION ALL
    SELECT 'No size here' UNION ALL
    SELECT '10x12'
),
Step1 AS (
    SELECT 
        InputString,
        -- 找到x/X的位置
        PATINDEX('%[xX]%', InputString) AS XPos
    FROM TestData
    WHERE PATINDEX('%[xX]%', InputString) > 0
),
Step2 AS (
    SELECT 
        InputString,
        -- 提取左侧数字部分
        LTRIM(RTRIM(SUBSTRING(InputString, 1, XPos - 1))) AS LeftPart,
        -- 提取右侧数字部分
        LTRIM(RTRIM(SUBSTRING(InputString, XPos + 1, LEN(InputString)))) AS RightPart
    FROM Step1
)
SELECT 
    InputString,
    CASE 
        WHEN (PATINDEX('%[0-9] %[0-9]/[0-9]%', LeftPart) > 0 OR PATINDEX('%[0-9]/[0-9]%', LeftPart) > 0 OR ISNUMERIC(LeftPart) = 1)
         AND (PATINDEX('%[0-9] %[0-9]/[0-9]%', RightPart) > 0 OR PATINDEX('%[0-9]/[0-9]%', RightPart) > 0 OR ISNUMERIC(RightPart) = 1)
        THEN CONCAT(LeftPart, ' x ', RightPart)
        ELSE NULL
    END AS ExtractedSize
FROM Step2;

注意事项 #

• 如果输入字符串中有多个x/X，上述XQuery方案会提取第一个符合规则的尺寸对；如需处理多个匹配，可调整XQuery的循环逻辑。
• ISNUMERIC函数在处理分数时会返回0，所以必须单独判断分数格式（PATINDEX('%[0-9]/[0-9]%', ...)）。

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