cross-dbms way to check if string is numeric

Question

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?

Do you have to worry about characters such as `.` 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 thinking `NOT LIKE '%[^0-9]%'` but looks like that may well be SQL Server specific :-( — Martin Smith, Jan 14 '11 at 09:28
Guessing something Standard SQL-92 e.g. `CAST(code AS NUMERIC(x, y))` with suitable values for `x` and `y`. — onedaywhen, Jan 14 '11 at 10:14
I am only interested in strings containing all digits and nothing more — jonny, Jan 14 '11 at 12:55

RichardTheKiwi · Accepted Answer · 2011-01-15T22:55:42.940

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

@jonny -if you are after digits only, then I have updated the query with a simpler test — RichardTheKiwi, Jan 14 '11 at 19:03
@cyberwiki: while `LIKE` is standard SQL, support for regular expressions is **not** standard and will not work with other databases (Oracle, Postgres, Firebird, DB2). I don't think MySQL supports it either, but I'm not sure about that. — , Jan 14 '11 at 19:36
@a_horse_with_no_name - I have been around long enough to know that LIKE is NOT REGEXP. MySQL and Oracle have specific REGEXP support that SQL Server does not. LIKE is just SQL, and "%" is hardly regexp, since ".*" is the regexp for what "%" is for LIKE. — RichardTheKiwi, Jan 14 '11 at 19:46
I just wanted to avoid confusion on where your example will work. You have written *LIKE [...] work in MySQL and Oracle` which is correct, just not the way you are using it in the example. `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*". — , Jan 14 '11 at 19:54
@a_h You are right. Answer updated. Got around to testing in MySQL — RichardTheKiwi, Jan 15 '11 at 22:49

cross-dbms way to check if string is numeric

1 Answers1

Linked