Search code examples
sql-serverisnumeric

Inconsistent result from isnumeric

I have the following queries.

    select ISNUMERIC(result+ 'E0'), from t1  
    select ISNUMERIC('7' + 'E0') 
    select ISNUMERIC('7' + '.E0')

The data type of result column is varchar(50).The first query yields 0 even when result is like 2,3 and returns 1 for float only ...Whereas second and third query works fine for both integer and float.Am I missing anything? My requirement is to check whether result column is number(integer,float) or not.I know isnumeric returns 1 for type like money,small money,real etc but this is not the case here as i don't have such type in my result and i am only receiving 0.

Solution

  • The reason for the seemingly inconsistent result might be, that there is a space in your result column value. Try timming text and feed the trimmed text to ISNUMERIC:

    select ISNUMERIC(ltrim(rtrim(result))+ 'E0') from t1