Sybase: Cannot convert after checking with isnumeric

If you have numeric and not numeric values and what to the isnumeric function to find out which values you can convert, pay attention. Isnumeric is not always reliable. Here an example:

1> create table benohead(almostnum varchar(10))
2> go
1> insert into benohead values('e')
2> go
(1 row affected)
1> select isnumeric(almostnum) from benohead
2> go

 -----------
           1

(1 row affected)
1> select convert(int, almostnum) from benohead where isnumeric(almostnum) = 1
2> go
Msg 249, Level 16, State 1:
Server 'SYBASE', Line 1:
Syntax error during explicit conversion of VARCHAR value 'e' to a INT field.

So in this case isnumeric returns 1 i.e. true. But you still can’t convert it to an int. Converting to a float, money or real will also fail:

1> select convert(float, almostnum) from benohead where isnumeric(almostnum) = 1
2> go
Msg 249, Level 16, State 1:
Server 'SYBASE', Line 1:
Syntax error during explicit conversion of VARCHAR value 'e' to a FLOAT field.
1> select convert(money, almostnum) from benohead where isnumeric(almostnum) = 1
2> go
Msg 249, Level 16, State 1:
Server 'SYBASE', Line 1:
Syntax error during explicit conversion of VARCHAR value 'e' to a MONEY field.
1> select convert(real, almostnum) from benohead where isnumeric(almostnum) = 1
2> go
Msg 249, Level 16, State 1:
Server 'SYBASE', Line 1:
Syntax error during explicit conversion of VARCHAR value 'e' to a REAL field.

But converting to a decimal value will work (well it won’t throw an error):

1> select convert(decimal, almostnum) from benohead where isnumeric(almostnum) = 1
2> go

 ---------------------
                     0

(1 row affected)

Of course, it depends whether you actually mean 0 when writing e in the column… It will not throw an error but might deliver wrong results…

3 thoughts on “Sybase: Cannot convert after checking with isnumeric

    1. The problem with this approach is that you miss some values which are not valid floats e.g. 1.2.3 or 1..2. It will also report values with an e as not being a valid float value e.g. 1e5. But of course, if you know that this kind of values will not be present in the database, then using such NOT LIKE statements is fine.

Leave a Reply

Your email address will not be published.