The forum will be unavailable for maintenance at some point from Friday, April 13 at 19:00 EDT until Sunday, April 15 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

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)

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 '17, 10:42

gchq's gravatar image

accept rate: 33%

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)?

permanent link

answered 13 Feb '17, 02:20

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

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

(13 Feb '17, 09:46) gchq
Replies hidden

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.

(13 Feb '17, 10:03) Volker Barth

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

(13 Feb '17, 14:26) gchq

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:

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

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.


permanent link

answered 13 Feb '17, 08:24

Mark%20Culp's gravatar image

Mark Culp
accept rate: 40%

edited 13 Feb '17, 08:25

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:

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

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

(13 Feb '17, 09:16) Volker Barth
Replies hidden

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

(13 Feb '17, 09:19) Mark Culp

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

(13 Feb '17, 09:41) gchq
Replies hidden

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.

(13 Feb '17, 09:47) Mark Culp
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 12 Feb '17, 10:42

question was seen: 408 times

last updated: 13 Feb '17, 14:26