SQL Server中如何判断值的最后4位是否在指定范围内

Absolutely! You don’t need to manually list all 879 values—there’s a far cleaner approach by combining string validation and numeric range checks. Here’s how to implement it:

Core Logic Breakdown #

We can split the problem into two simple checks:

1. First, verify the last 4 characters of col1 are pure numeric digits (any non-numeric substring automatically fails the range test).
2. Convert those 4 digits to a numeric value, then check if it falls between 1 and 879 (leading zeros in the string don’t affect the numeric value, so 0001 maps to 1 and 0879 maps to 879).

Universal SQL Query #

This version works across most major SQL dialects:

SELECT
  col1,
  column2,
  CASE
    -- Check if last 4 characters are all digits
    WHEN RIGHT(col1, 4) REGEXP '^[0-9]{4}$'
      -- Convert to number and validate range
      THEN CASE WHEN CAST(RIGHT(col1, 4) AS UNSIGNED) BETWEEN 1 AND 879 THEN 'True' ELSE 'False' END
    -- Non-numeric last 4 characters = automatically False
    ELSE 'False'
  END AS column3
FROM your_table;

Dialect-Specific Optimizations #

For better compatibility with your database, use these tailored versions:

MySQL/MariaDB

Leverage IF for more concise syntax:

SELECT
  col1,
  column2,
  CASE
    WHEN RIGHT(col1, 4) REGEXP '^[0-9]{4}$'
      THEN IF(CAST(RIGHT(col1,4) AS UNSIGNED) BETWEEN 1 AND 879, 'True', 'False')
    ELSE 'False'
  END AS column3
FROM your_table;

SQL Server

Use TRY_CAST to gracefully handle non-numeric values:

SELECT
  col1,
  column2,
  CASE
    WHEN TRY_CAST(RIGHT(col1, 4) AS INT) IS NOT NULL
      THEN CASE WHEN TRY_CAST(RIGHT(col1, 4) AS INT) BETWEEN 1 AND 879 THEN 'True' ELSE 'False' END
    ELSE 'False'
  END AS column3
FROM your_table;

PostgreSQL

Use TRY_TO_NUMBER for safe conversion:

SELECT
  col1,
  column2,
  CASE
    WHEN TRY_TO_NUMBER(RIGHT(col1, 4), '9999') IS NOT NULL
      THEN CASE WHEN TRY_TO_NUMBER(RIGHT(col1, 4), '9999') BETWEEN 1 AND 879 THEN 'True' ELSE 'False' END
    ELSE 'False'
  END AS column3
FROM your_table;

Test with Your Sample Data #

Running this against your initial table produces exactly your expected result:

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