如何用SQL Server查询判断数值是否在字符串格式的范围内

核心思路 #

要实现Table B数值与Table A字符串范围的匹配，需分三步处理：拆分范围字符串→解析每个范围的上下限→统一数值格式后做区间匹配。

规则梳理 #

先明确范围解析的核心逻辑（最终统一为8位数值区间）：

• 单个数字串（如230910、250010）：
  • 下限：数字串补0至8位（如230910→23091000）
  • 上限：数字串补9至8位（如230910→23091099）
• 数字区间（如01-05、2302-2310）：
  • 下限：区间左值补0至8位（如01→01000000）
  • 上限：区间右值补9至8位（如05→05999999）

Table B的数值需统一转换为8位数值（补前导零至8位后转成数值类型），再判断是否落在任意一个解析后的区间内。

具体SQL实现 #

假设Table A结构为(id INT, range_str NVARCHAR(MAX))，Table B结构为(value_str NVARCHAR(20))，以下是完整查询代码：

WITH ParsedRanges AS (
    -- 拆分逗号分隔的范围字符串，去除每个单元的空格
    SELECT 
        a.id,
        TRIM(s.value) AS range_unit
    FROM TableA a
    CROSS APPLY STRING_SPLIT(a.range_str, ',') s
),
RangeBounds AS (
    -- 解析每个范围单元的上下限
    SELECT
        pr.id,
        -- 计算下限
        CASE 
            WHEN CHARINDEX('-', pr.range_unit) > 0 THEN 
                CAST(LEFT(pr.range_unit, CHARINDEX('-', pr.range_unit)-1) + REPLICATE('0', 8 - LEN(LEFT(pr.range_unit, CHARINDEX('-', pr.range_unit)-1))) AS BIGINT)
            ELSE
                CAST(pr.range_unit + REPLICATE('0', 8 - LEN(pr.range_unit)) AS BIGINT)
        END AS lower_bound,
        -- 计算上限
        CASE 
            WHEN CHARINDEX('-', pr.range_unit) > 0 THEN
                CAST(RIGHT(pr.range_unit, LEN(pr.range_unit)-CHARINDEX('-', pr.range_unit)) + REPLICATE('9', 8 - LEN(RIGHT(pr.range_unit, LEN(pr.range_unit)-CHARINDEX('-', pr.range_unit)))) AS BIGINT)
            ELSE
                CAST(pr.range_unit + REPLICATE('9', 8 - LEN(pr.range_unit)) AS BIGINT)
        END AS upper_bound,
        pr.range_unit
    FROM ParsedRanges pr
),
BValues AS (
    -- 将Table B的数值转换为8位标准数值
    SELECT
        value_str,
        CAST(RIGHT(REPLICATE('0',8) + value_str, 8) AS BIGINT) AS value_num
    FROM TableB
)
-- 最终匹配查询
SELECT
    b.value_str,
    CASE WHEN EXISTS (
        SELECT 1 
        FROM RangeBounds rb
        WHERE b.value_num BETWEEN rb.lower_bound AND rb.upper_bound
    ) THEN 'x' ELSE '' END AS result,
    -- 可选：拼接匹配的范围单元作为说明
    STUFF((
        SELECT ', ' + rb.range_unit
        FROM RangeBounds rb
        WHERE b.value_num BETWEEN rb.lower_bound AND rb.upper_bound
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS matched_ranges
FROM BValues b;

关键说明 #

1. 字符串拆分：使用STRING_SPLIT（SQL Server 2016+支持）拆分逗号分隔的范围，若版本较低需替换为自定义拆分函数。
2. 数值统一：通过补前导零将Table B的数值转为8位BIGINT，确保所有数值在同一维度比较。
3. 区间判断：用BETWEEN语句判断数值是否落在解析后的区间内，EXISTS确保只要匹配任意一个范围就返回标记。
4. 性能优化：若数据量较大，可将RangeBounds的结果存入临时表并添加lower_bound和upper_bound的索引，提升匹配效率。

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