Cross-dbms way to check if a string is numeric

Question

Ok, I have this field: code varchar(255) . It contains some values ​​used in our export procedure, for example

 DB84 DB34 3567 3568 

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

 WHERE is_numeric(table.code) 

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

Can you suggest something that will work in both mysql 5.1 and oracle 10g?

Answer 1

The following are three separate implementations for each SQL Server, MySQL, and Oracle. None of them use (or can) use the same approach, so there seems to be no cross-DBMS method for this. For MySQL and Oracle, only a simple integer test is shown; For SQL Server, a complete numerical test is shown.

For SQL Server: note that isnumeric ('.') Returns 1 .. but it cannot actually be converted to float. Some text, such as "1e6", cannot be converted to a numeric number directly, but you can go through a float, then a numeric one.

 ;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 

For 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 

For Oracle

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