跨dbms检查字符串是否为数字的方法 [英] cross-dbms way to check if string is numeric

问题描述

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

Ok, I have this field: code varchar(255). It contains some values used in our export routine like

DB84
DB34
3567
3568

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

I need to select only auto-generated (fully numeric) fields

WHERE is_numeric(table.code)

is_numeric()检查code字段是否仅包含正数.

is_numeric() checks if code field contains only positive digits.

您能提出在mysql 5.1和oracle 10g下都可以使用的任何建议吗?

Can you propose anything that will work both under mysql 5.1 and oracle 10g?

推荐答案

下面是针对SQL Server，MySQL和Oracle的三个单独的实现.没有人使用(或可以)使用相同的方法，因此似乎没有跨DBMS的方法. 对于MySQL和Oracle，仅显示简单的整数测试.对于SQL Server，将显示完整的数字测试.

Below are three separate implementations for each of SQL Server, MySQL and Oracle. None use (or can) the same approach, so there doesn't seem to be a cross DBMS way to do it. For MySQL and Oracle, only the simple integer test is show; for SQL Server, the full numeric test is shown.

对于SQL Server: 请注意isnumeric('.')返回1 ..，但实际上不能转换为float.某些文本(例如"1e6")无法直接转换为数字，但可以先通过浮点数再通过数字.

For SQL Server: note that isnumeric('.') returns 1.. but it can not actually be converted to float. Some text like '1e6' cannot be converted to numeric directly, but you can pass through float, then numeric.

;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

对于Oracle

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

这篇关于跨dbms检查字符串是否为数字的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！