How can I check if something is not a number

Dan_Rosen · 2025-03-12T15:09:49+00:00

There was an error rendering this rich post.

With an excel input, I wanted to validate that the values are numeric, and in the case where the user has a string, error, or blank in the cell I want to cast it as something else.

I tried the IsNaN function but it didn't work as I expected. I did IsNaN('test') and it returns false, so that doesn't really help me.

Any ideas? Thanks

Find more posts tagged with

Accepted answers

All comments

Diane McElhiney

Dan,

The result is correct in your example of IsNan('test'). False = numeric; True = not numeric as stated in the on-line doc:

"Evaluates an expression and returns TRUE if it is not a number and FALSE if it is. The result can be used as a conditional check."

I've used the below data check flow and had correct results.

IF ISNAN(INTEGER(SUBSTRING(somecolumn,0,8))) THEN

[outputcolumn] = NULL

ELSE

IF [somecolumn] IS NOT NULL THEN

[outputcolumn] = DATETIME(FORMAT(DATETIME(SUBSTRING([somecolumn],0,8)),'yyyy-MM-dd'))

END IF

Dan_Rosen

I might be able to make it work with something like this (inspired by your comments):

ISNAN(FLOAT(SUBSTRING('test',0)))

I will play around a bit and see. Thanks!

EDIT:

A simpler way for my situation might actually just be using this:

ISNAN(FLOAT('test'))

first checking if it's null before doing so.

I will test further.

Dan_Rosen

I ended up with something like:

IF [inputfield] IS NULL
OR ISNAN(FLOAT([inputfield])) THEN
[outputfield] = 0.0
ELSE
[outputfield] = [inputfield]
END IF

Will see if it holds up 😀

Enrico Menotti

@Diane McElhiney 'test' is a string, so not a number: I'd expect ISNAN('test') to return True. Don't you agree?

@Dan_Rosen Indeed I played with it around a bit and it looks like ISNAN returns True when the argument is of numeric datatype but not a valid number. If the datatype is not numeric (I tried with a string, i.e. Varchar), it seems to be returning False regardless. I'd do:
ISNAN(NUMBER([inputfield]))
if you have a precise decimal. ISNAN(NUMBER('test')) returns True. NUMBER(NULL) returns Cannot cast to number so it's likely that you have to handle nulls separately, indeed.

I guess that if [inputfield] is already of numeric datatype in the source, you don't need the NUMBER(), but I didn't actually try. Incidentally, ISNAN(NULL) returns True, so if you don't need the NUMBER() it's likely that you also don't need to handle nulls separately.

Enrico

Dan_Rosen

I am expecting a number/float but I know that the input could be a string or null, so Birst will input the source as varchar regardless, which makes it necessary to check for both cases

Enrico Menotti

Even if in the source definition you set the column as a number?

Diane McElhiney

From the Quick BQL Reference as I stated in my prior comment:

"Evaluates an expression and returns TRUE if it is not a number and FALSE if it is. The result can be used as a conditional check."

Enrico Menotti

Yes. Now, 'test' is a string, so not a number. Then, ISNAN('test') should return True. However, it returns False.

Dan_Rosen

Didn't try that actually. I was afraid that it would yell on the import