IsNumeric returning blank instead of zero

[former_SQLA_member1694879]

If I use a simple line like this

strSQL = "SELECT IsNumeric(Item_ID) FROM Management_Associations WHERE Management_ID = 1 AND Association_ID = " & Session("HOAID")

based on the documentation (albeit for an older version)

http://dcx.sap.com/1001/en/dbrfen10/rf-isnumeric-misc-functions.html

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?

Thanks

[VolkerBarth · Accepted solution]

In my tests (with v16.0.0.2344), 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)?

[former_SQLA_member1694879 · Accepted solution]

I'm sure the result set would be empty as I'm testing to see if a record exists - I'm assuming (probably incorrectly) that an empty record set is the same as blank? As it happens setting the value of an integer to zero then running it against that query will maintain the value of zero with no hits so it still works

[VolkerBarth · Accepted solution]

No, these are (at least) 3 different concepts:

• an "empty" value is a string of length 0 (i.e. '')
• a "blank" is a particular character (space) or a string consisting of one space, so of length 1 (i.e. ' ')
• a NULL value is a special value used to represent missing or inapplicable information
• an empty result set means no row has fulfilled the conditions of your query. Note: It is a result set, not a value.

---

AFAIK, the COUNT() aggregate function is the only one to return a non-NULL value when applied to an empty result set, COUNT() will return 0 then.

[former_SQLA_member1694879 · Accepted solution]

I guess that really COUNT() is what I should be using - Thank you.

[MarkCulp]

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:

begin
  declare @s long varchar;
  set @s = ( select table_id from systable where table_name = 'foobar' );
  select isnull( @s, '-null-' );
end;

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.

HTH

[VolkerBarth]

When the query matches no rows then the value returned is NULL.

Is that generally true? I would think it depends whether the result is directly assigned to a SQL variable in contrast to access via cursors and the like.

I.e. when a cursor is used, it should return a SQLCODE 100 warning, as no rows are fetched at all, such as in this sample based on my Test table:

begin
   declare err_notfound
      exception for sqlstate '02000';
   declare bIsNumeric bit;
   declare crs cursor for select isnumeric(pk) from Test where pk = 0;
   open crs;
lpCrs:
   loop
      fetch next crs
         into bIsNumeric;
      if sqlstate = err_notfound then
         message 'No row found' to client;
         leave lpCrs;
      else
         select bIsNumeric;
      end if;
   end loop lpCrs; 
   close crs;
end;

I do not know what API is used by gchq, but often enough client APIs do tend to use cursors under the covers...

[MarkCulp]

You are correct. My comment was made in the context of the example that I gave.

[former_SQLA_member1694879]

According to the documentation

ISNUMERIC returns 1 when the input string evaluates to a valid integer or floating point number; otherwise it returns 0. The function also returns 0 if the string contains only blanks or is NULL.

It should always return a zero if the result is blank or null

[MarkCulp]

The documentation is correct. As I said in my answer, the issue in your example (as I understand it) is that there are no rows returned by your query and therefore the query does not even get to the stage of evaluating the isnumeric expression - i.e. it is not executed - because there is no row, hence no value, in which to evaluate! In these circumstances the returned value (from a subquery evaluation) will be NULL.