SQLite Forum

Efficiently checking if a TEXT column exclusively contains integers or floating point numbers (as strings)
Login
```
>gcc -s -mdll -m64 -I/source/bld sqlnumeric.c -o sqlnumeric.dll

>sqlite
SQLite version 3.34.0 2020-09-26 18:58:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load sqlnumeric
sqlite> create table x(x);
sqlite> insert into x values ('0');
sqlite> insert into x values ('-0.01');
sqlite> insert into x values ('-1');
sqlite> insert into x values ('-1.01');
sqlite> insert into x values ('1e12');
sqlite> insert into x values ('-1e12');
sqlite> insert into x values ('1e-12');
sqlite> insert into x values ('-1e-12');
sqlite> insert into x values ('1.1e-12');
sqlite> insert into x values ('-1.1e-12');
sqlite> insert into x values ('1brick');
sqlite> insert into x values ('-1brick');
sqlite> select x, typeof(y), y
   ...>   from (
   ...>         select x,
   ...>                ToBestType(x) as y
   ...>           from x
   ...>        )
   ...> ;
┌──────────┬───────────┬────────────────┐
│    x     │ typeof(y) │       y        │
├──────────┼───────────┼────────────────┤
│ 0        │ integer   │ 0              │
│ -0.01    │ real      │ -0.01          │
│ -1       │ integer   │ -1             │
│ -1.01    │ real      │ -1.01          │
│ 1e12     │ integer   │ 1000000000000  │
│ -1e12    │ integer   │ -1000000000000 │
│ 1e-12    │ real      │ 1.0e-12        │
│ -1e-12   │ real      │ -1.0e-12       │
│ 1.1e-12  │ real      │ 1.1e-12        │
│ -1.1e-12 │ real      │ -1.1e-12       │
│ 1brick   │ text      │ 1brick         │
│ -1brick  │ text      │ -1brick        │
└──────────┴───────────┴────────────────┘
sqlite> create table y(x, y numeric);
sqlite> insert into y select x,x from x;
sqlite> select x, typeof(y), y from y;
┌──────────┬───────────┬────────────────┐
│    x     │ typeof(y) │       y        │
├──────────┼───────────┼────────────────┤
│ 0        │ integer   │ 0              │
│ -0.01    │ real      │ -0.01          │
│ -1       │ integer   │ -1             │
│ -1.01    │ real      │ -1.01          │
│ 1e12     │ integer   │ 1000000000000  │
│ -1e12    │ integer   │ -1000000000000 │
│ 1e-12    │ real      │ 1.0e-12        │
│ -1e-12   │ real      │ -1.0e-12       │
│ 1.1e-12  │ real      │ 1.1e-12        │
│ -1.1e-12 │ real      │ -1.1e-12       │
│ 1brick   │ text      │ 1brick         │
│ -1brick  │ text      │ -1brick        │
└──────────┴───────────┴────────────────┘
sqlite>
```