如何在SQL中无需IN语句筛选指定长度的.lyr后缀数字记录？

我的数据库表中有一列，存储的是所有1至6位数字组合后拼接.lyr的内容，比如0.lyr、1.lyr、00.lyr、11.lyr直到999999.lyr。我想写SQL查询语句获取所有4位数字组合的记录（范围是0000.lyr至9999.lyr），请问有没有不用IN语句的实现方式？

当然有，不用IN的话，直接通过字符串规则校验就能实现。核心是确认两条：前缀是恰好4位数字，后缀是.lyr。下面是几种主流数据库的具体写法：

MySQL/MariaDB #

SELECT *
FROM 你的表名
WHERE 
  SUBSTRING_INDEX(你的列名, '.', 1) REGEXP '^[0-9]{4}$'
  AND 你的列名 LIKE '%.lyr';

• SUBSTRING_INDEX用来提取.前面的数字部分
• REGEXP '^[0-9]{4}$'确保这部分是正好4位数字
• LIKE '%.lyr'兜底确认后缀格式正确

SQL Server #

基础写法 #

SELECT *
FROM 你的表名
WHERE 
  LEN(LEFT(你的列名, CHARINDEX('.', 你的列名) - 1)) = 4
  AND ISNUMERIC(LEFT(你的列名, CHARINDEX('.', 你的列名) - 1)) = 1
  AND 你的列名 LIKE '%.lyr';

更严谨的写法（SQL Server 2012+） #

如果担心ISNUMERIC误判非整数的情况，用TRY_CAST更靠谱：

SELECT *
FROM 你的表名
WHERE 
  LEN(LEFT(你的列名, CHARINDEX('.', 你的列名) - 1)) = 4
  AND TRY_CAST(LEFT(你的列名, CHARINDEX('.', 你的列名) - 1) AS INT) IS NOT NULL
  AND 你的列名 LIKE '%.lyr';

PostgreSQL #

SELECT *
FROM 你的表名
WHERE 
  SPLIT_PART(你的列名, '.', 1) ~ '^[0-9]{4}$'
  AND 你的列名 LIKE '%.lyr';

• SPLIT_PART提取.前的数字部分
• ~ '^[0-9]{4}$'用正则验证是4位数字

极简写法（前提：所有记录后缀都是.lyr） #

如果你能保证表中没有不符合.lyr后缀的数据，还可以直接通过总长度判断：
4位数字加.lyr总长度是8（比如0000.lyr就是8个字符），所以：

-- MySQL用LENGTH，SQL Server用LEN，PostgreSQL两者都可
SELECT *
FROM 你的表名
WHERE 
  LENGTH(你的列名) = 8
  AND SUBSTRING(你的列名, 1, 4) REGEXP '^[0-9]+$';

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