If I use a simple line like this
based on the documentation (albeit for an older version)
I would expect it to return either 0 or 1. However a 'no hit' just returns a blank not a zero.. How can I get this to work as documented?
asked 12 Feb, 10:42
In my tests (with v184.108.40.2064), isnumeric() does return 0 or 1, not null, even for null as argument.
drop table if exists Test; create table Test ( pk int not null primary key, col2 varchar(10) not null ); insert Test values (1, 'Hi'); select isnumeric(pk), isnumeric(col2), isnumeric(null) from Test; -- returns 1, 0, 0 select isnumeric(pk), isnumeric(col2), isnumeric(null) from Test where pk = 0; -- returns an empty result set
Are you sure your result set is not simply empty (as in my second select)?
answered 13 Feb, 02:20
I think the key words in the questions are 'no hit' meaning that no rows matched the search conditions and therefore the result set is empty.
When the query matches no rows then the value returned is NULL. This is normal and has always been the case. The behaviour that you are seeing has nothing to do with the isnumeric function.
An easy way to demonstrate this is:
Assuming you don't actually have a table named 'foobar' in your database, the above batch will show '-isnull-' when you execute it in dbisql[c]. Changing table_id to isnumeric( table_id ) in the above select query makes no difference to the result (returning '-isnull-') because no rows were selected so the isnumeric( table_id ) expression never gets evaluated at all.