sql - 检查字符串是否为数字的跨数据库方法

Question

好的，我有这个字段:code varchar(255)。它包含一些在我们的导出例程中使用的值，例如

DB84
DB34
3567
3568

我只需要选择自动生成的(全数字)字段

WHERE is_numeric(table.code)

is_numeric() 检查 code 字段是否只包含正数。

你能提出任何在 mysql 5.1 和 oracle 10g 下都可以工作的建议吗？

Answer 1

下面是 SQL Server、MySQL 和 Oracle 各自的三个独立实现。没有人使用(或可以)相同的方法，因此似乎没有跨 DBMS 的方法来做到这一点。对于 MySQL 和 Oracle，只显示简单的整数测试；对于 SQL Server，显示了完整的数值测试。

对于 SQL 服务器:请注意，isnumeric('.') 返回 1.. 但它实际上不能转换为 float 。有些文本如 '1e6' 不能直接转换为数字，但可以通过 float，然后是数字。

;with tmp(x) as (
    select 'db01' union all select '1' union all select '1e2' union all
    select '1234' union all select '' union all select null union all
    select '1.2e4' union all select '1.e10' union all select '0' union all
    select '1.2e+4' union all select '1.e-10' union all select '1e--5' union all
    select '.' union all select '.123' union all select '1.1.23' union all
    select '-.123' union all select '-1.123' union all select '--1' union all
    select '---1.1' union all select '+1.123' union all select '++3' union all
    select '-+1.123' union all select '1 1' union all select '1e1.3' union all
    select '1.234' union all select 'e4' union all select '+.123' union all
    select '1-' union all select '-3e-4' union all select '+3e-4'  union all
    select '+3e+4' union all select '-3.2e+4' union all select '1e1e1' union all
    select '-1e-1-1')

select x, isnumeric(x),
    case when x not like '%[^0-9]%' and x >'' then convert(int, x) end as SimpleInt,
    case
    when x is null or x = '' then null -- blanks
    when x like '%[^0-9e.+-]%' then null -- non valid char found
    when x like 'e%' or x like '%e%[e.]%' then null -- e cannot be first, and cannot be followed by e/.
    when x like '%e%_%[+-]%' then null -- nothing must come between e and +/-
    when x='.' or x like '%.%.%' then null -- no more than one decimal, and not the decimal alone
    when x like '%[^e][+-]%' then null -- no more than one of either +/-, and it must be at the start
    when x like '%[+-]%[+-]%' and not x like '%[+-]%e[+-]%' then null
    else convert(float,x)
    end
from tmp order by 2, 3

对于 MySQL

create table tmp(x varchar(100));
insert into tmp
    select 'db01' union all select '1' union all select '1e2' union all
    select '1234' union all select '' union all select null union all
    select '1.2e4' union all select '1.e10' union all select '0' union all
    select '1.2e+4' union all select '1.e-10' union all select '1e--5' union all
    select '.' union all select '.123' union all select '1.1.23' union all
    select '-.123' union all select '-1.123' union all select '--1' union all
    select '---1.1' union all select '+1.123' union all select '++3' union all
    select '-+1.123' union all select '1 1' union all select '1e1.3' union all
    select '1.234' union all select 'e4' union all select '+.123' union all
    select '1-' union all select '-3e-4' union all select '+3e-4'  union all
    select '+3e+4' union all select '-3.2e+4' union all select '1e1e1' union all
    select '-1e-1-1';

select x,
    case when x not regexp('[^0-9]') then x*1 end as SimpleInt
from tmp order by 2

甲骨文

case when REGEXP_LIKE(col, '[^0-9]') then col*1 end