如何使用SQL识别长整型数值并判断是否存在指数格式值

核心问题说明 #

ISNUMERIC()函数会将合法的科学计数法字符串（例如5.02E+13）判定为有效数字，无法满足区分纯长整型和指数格式的需求。

可用实现方案 #

• 方案1：纯数字模式匹配（兼容性最好，无版本限制）
  直接校验上传字符串是否仅由数字组成，只要包含非数字字符就判定为非法，适配需要拦截指数、小数点等非纯整数场景的需求：

  -- 从临时表中查询所有非法格式的记录
  SELECT * 
  FROM #你的临时表名
  -- 匹配是否存在0-9之外的字符，返回大于0即存在非法字符
  WHERE PATINDEX('%[^0-9]%', TRIM(上传字段名)) > 0
  -- 可额外加长度校验，例如要求数值为14位就加上以下条件
  -- OR LEN(TRIM(上传字段名)) != 14
  

  适用场景：所有SQL Server版本，支持超长数值（超出BIGINT范围的数值也可校验）。

• 方案2：TRY_CAST 类型转换校验（SQL Server 2012及以上版本支持）
  利用类型转换的严格性判定是否为合法长整型，指数格式字符串无法直接转为BIGINT类型，会返回NULL：

  SELECT *
  FROM #你的临时表名
  WHERE TRY_CAST(TRIM(上传字段名) AS BIGINT) IS NULL
  

  注意：如果你的上传数值超出BIGINT的最大范围（大于9223372036854775807），不要使用该方案，优先选方案1。

• 方案3：精准拦截指数格式字符串
  如果你只需要拦截科学计数法格式，其他带小数点、符号的数值不需要拦截，可以用该规则专门匹配指数格式特征：

  SELECT *
  FROM #你的临时表名
  WHERE 上传字段名 LIKE '%E[+-]%' OR 上传字段名 LIKE '%e[+-]%'
  

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