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() checks if code field contains only positive digits.
Can you propose anything that will work both under mysql 5.1 and oracle 10g?
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.
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
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
like '%[^0-9e.+-]%' makes use of a regex "range" and that part will only work with SQL Server. You should add a third item that read "*remove the usage of regex ranges in the example".
.and-or are you only interested in numbers containing all digits? (i.e. positive integers) Edit: I'm not sure it matters actually I was thinkingNOT LIKE '%[^0-9]%'but looks like that may well be SQL Server specific :-(CAST(code AS NUMERIC(x, y))with suitable values forxandy.