ISNUMERIC returns odd result

[JamesLean]

I started off posting this as a question but have since realised what is happening (I think). I decided to post anyway as it's something to be aware of.

I was inserting some character data into a money column, using ISNUMERIC first to check whether it could be converted to a number. However I got an error saying that the value '3E000' could not be converted to money. For some reason that value was returning 1 from the function, ie it was saying it WAS numeric. The convert was then failing.

I was confused as according to BOL:

ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types.

I tried a few other random values containing an 'E' and get the following results:

[tt]VALUE ISNUMERIC
----------------
E 0
1E 0
1E0 1
18E13 1
0E2 1[/tt]

Initially, I thought it might be something to do with HEX values but couldn't find an example where any other letter returned a 1.

I then realised that it might be reading the E as the exponential symbol in scientific number notation. Lo and behold I executed:

SELECT CAST('3E000' AS float)

and got a valid result back: 3.0

So ISNUMERIC does guarantee the value can be converted into one of the numeric data types, just not necessarily all of them!

Hope this saves someone a couple of hours of exasperation (too late for me I'm afraid...) ;-)

--James

 

[vongrunt]

AFAIK same applies to letter "D" (ol' Fortran-like scientific notation, double precision).

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[dbomrrsm]

Thanks for posting this James - these things are always handy to know - as uou never can tell when it might happen tou you !

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[JamesLean]

Indeed. Plus I'd composed most of the post before I worked out what was going on so it seemed a bit of a waste to delete it!

--James

 

[donutman]

[Blue]SELECT[/Blue] [Fuchsia]IsNumeric[/Fuchsia][Gray]([/Gray][red]'$1.20'[/red][Gray])[/Gray]

Returns 1. Would have been nice if BOL had written:

ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to [red]at least[/red] one of these numeric types.

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Let's go international:

SELECT IsNumeric('€1.20')
SELECT IsNumeric('£1.20')

-- banzai!
SELECT IsNumeric('$1.20' collate Japanese_CI_AI)
SELECT IsNumeric('€1.20' collate Japanese_CI_AI)

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[ESquared]

SELECT IsNumeric(',')

 

[donutman]

select convert(money,',')

They haven't lied yet.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

At some point you have to ask, exactly what good is the function IsNumeric()?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dbomrrsm]

Still no lie !!!

SELECT IsNumeric(char(9))
SELECT IsNumeric(char(10))
SELECT IsNumeric(char(13))

select convert(money,char(9))
select convert(money,char(10))
select convert(money,char(13))

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[vongrunt]

Oh frell... [flush]

declare @t table( n smallint ); insert into @t values (0)

while (select count(*) from @t) < 256
	insert into @t select (select count(*) from @t)+n from @t

select n, char(n)
from @t
where isnumeric(char(n)) = 1 
	and n not between 48 and 57
order by n

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

I've got the solution. They need to rewrite the function and allow it to have two parameters.
IsNumeric(',',money)
IsNumeric(',',int)
etc.
That would be very handy indeed.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dbomrrsm]

Karl

Should I have a go at a UDF to do just that ? ;-)

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[ESquared]

Depending on the specific case, using some custom LIKE clauses might do the job more efficiently than a UDF.

[tt]WHERE Column NOT LIKE '%[^-0-9.]%' AND Column NOT LIKE '%[-.]%[-.]%' AND Column NOT LIKE '%[0-9]%-%'[/tt]

Or something like that (pun intended).

 

[donutman]

They LIED.

ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0

[Blue]DECLARE[/Blue] @V [Blue]AS[/Blue] [Blue]varchar[/Blue][Gray]([/Gray]10[Gray])[/Gray]
[Blue]SET[/Blue] @V[Gray]=[/Gray][Gray]NULL[/Gray]
[Blue]SELECT[/Blue] [Fuchsia]IsNumeric[/Fuchsia][Gray]([/Gray]@V[Gray])[/Gray]
[Blue]SELECT[/Blue] [Fuchsia]Convert[/Fuchsia][Gray]([/Gray][Blue]money[/Blue][Gray],[/Gray]@V[Gray])[/Gray]

Okay, it does depend upon the defination of evaluates and valid.
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

So ESquared how do you get something like this to work correctly:

[Blue]DECLARE[/Blue] @V [Blue]varchar[/Blue][Gray]([/Gray]10[Gray])[/Gray]
[Blue]SET[/Blue] @V[Gray]=[/Gray][red]' 123'[/red] [green]--and not ' 1 23' and yes to '- 123' etc
[/green][Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue] test [Blue]WHERE[/Blue] @V [Gray]NOT[/Gray] [Gray]LIKE[/Gray] [red]'%[^-0-9 ]%'[/red]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

ltrim(rtrim()) it first,
don't allow a space or a dash after a number '%[0-9]%[- ]%'

I've been wanting a more complicated LIKE syntax for a while now. Like [] is for a single character, make {} for multiple strings suchas

cowabunga
catabunga
dogabunga

select * from bungalist where bunga like '{,cow,cat}%' cow and cat
select * from bungalist where bunga like '{|^dog|cat}%' exclude dog and cat
And nested:

'{,ki{,ll,ss},pu{,sh,t}'
matches kill, kiss, push, put

also:
'sing{,^e,^ed,}%'
matches singing, singer, singwhatever, but not 'sing' and not 'singe' or 'singed.' Note the "not nothing" part in there, which might be nice to have in the single character syntax, too.

And how about ranges:
'{,car-cow}'
matches cat but not cap

Or repeats, or number of instances, etc. etc. etc.

Basically a partial implementation of complex pattern matching such as that used by proxy-server html prefiltering programs.

 

[donutman]

I find that pattern matching syntax difficult now. Add those features and I'd never find my way home.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

Blast it, I want these features, and if you find the current implementation difficult, you know that microsoft is never going to do anything which would make it that much more complex.

 

[JamesLean]

If you're gonna extend LIKE, why not go the whole hog and introduce a full REGEXP function. Now that would be useful!

--James