SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Ron Moses / More fun with ISNUMERIC() / Latest Posts

RE: More fun with ISNUMERIC()

L' Eomot Inversé — Sat, 18 Sep 2010 13:10:50 GMT

Nice question. I particularly liked the inclusion of the string in the final select, so that people could see what was going on.Surprised by some of the comments - I though everyone would know the what the character codes in the 9 to 13 range represent, I guess some of teh youngsters never needed to know that stuff because (a) modern text handling tools are a bit higher level that some of the old ones and (b) people stopped reading punched tape maybe 30 years ago.

RE: More fun with ISNUMERIC()

jawad.zubairi — Thu, 15 Jul 2010 12:31:07 GMT

It does as the MSDN says,ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uas9/html/5193c976-9dcd-459c-abba-8c3c44e7a7f2.htm ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols.

RE: More fun with ISNUMERIC()

pparsons — Wed, 07 Jul 2010 21:57:33 GMT

I'd be curious to see what the expected result is for the 21 count. I use SQL2K and long ago ended up creating my own IsNumeric function because of the issues with the native version, so assumed based on the comma issue the "right" answer was most likely 21 (comma is treated differently in SQL2K vs. SQL2K5). I read mention of the character 92, which was weird to me, and with the other variants on answers it only goes to show (IMO) the uselessness of this function

RE: More fun with ISNUMERIC()

Jeff Moden — Wed, 07 Jul 2010 18:14:48 GMT

[quote][b]Olga B (7/7/2010)[/b][hr]I also noticed that converting a string of numbers with a comma anywhere in it (e.g., '12,345') to a numeric value results in an error, even though ISNUMERIC returns 1 on that string.[/quote]Heh... "It depends"... ;-)[code="sql"] SELECT ISNUMERIC('1,2,3,4,5,6,7,8,9'), CAST('1,2,3,4,5,6,7,8,9' AS MONEY)[/code]By definition, ISNUMERIC will return a 1 if the operand can be converted to ANY numeric value using ANY numeric datatype conversion... not just the ones you expect.

RE: More fun with ISNUMERIC()

SQLRNNR — Wed, 07 Jul 2010 14:56:04 GMT

thanks for the QOD

RE: More fun with ISNUMERIC()

Olga B — Wed, 07 Jul 2010 13:42:12 GMT

[quote][b]Toreador (7/7/2010)[/b][hr]that's the bit I can't understand. Does the same not apply to[code="sql"]isnumeric('1.234,56')[/code]yet this returns 1?[/quote]I wonder if it's because ISNUMERIC disregards the position of the comma? For instance, [code="sql"]isnumeric('123,45.7')[/code]returns 1As does[code="sql"]isnumeric('12345.67,89')[/code]I also noticed that converting a string of numbers with a comma anywhere in it (e.g., '12,345') to a numeric value results in an error, even though ISNUMERIC returns 1 on that string.

RE: More fun with ISNUMERIC()

Toreador — Wed, 07 Jul 2010 02:14:17 GMT

[quote][b]Oleg Netchaev (7/6/2010)[/b][hr][code="sql"]select cast('1 234' as int);[/code]will never fly simply because it is not a valid way to represent the number in every locale (though it is valid in some of them).[/quote]that's the bit I can't understand. Does the same not apply to[code="sql"]isnumeric('1.234,56')[/code]yet this returns 1?

RE: More fun with ISNUMERIC()

ziangij — Wed, 07 Jul 2010 00:30:15 GMT

thanks Duncan :-)I also had the same issue... was getting the count 20 but didn't know why... its because of the compatibility level set to 80..

RE: More fun with ISNUMERIC()

KevinC. — Tue, 06 Jul 2010 16:08:04 GMT

Excellent question. Thanks.Also, thanks to Oleg and Duncan for their explanations.

RE: More fun with ISNUMERIC()

SanjayAttray — Tue, 06 Jul 2010 12:32:35 GMT

different answer for different sql versions.SQL 2005 and 2008 gives 21 while 2000 with nvarchar(8000) throws errorMsg 2717, Level 16, State 2, Line 1The size (8000) given to the parameter '@String' exceeds the maximum allowed (4000).Parameter '@String' has an invalid data type.giving varchar(8000) would give 20 answer.20 (9), (10), (11), (12), (13), $(36), +(43), ,(44), -(45), .(46), 0(48), 1(49), 2(50), 3(51), 4(52), 5(53), 6(54), 7(55), 8(56), 9(57), Thanks for a good question.

RE: More fun with ISNUMERIC()

Oleg Netchaev — Tue, 06 Jul 2010 11:17:30 GMT

[quote][b]Toreador (7/6/2010)[/b][hr]That doesn't explain why isnumeric('1 234') = 0, despite being in the standard format for any locale with a space as the thousands separator![/quote]What I mentioned in my post was related only to the trailing spaces behavior. In other words [code="sql"]select cast('1234 ' as int);[/code]is fine because the trailing spaces are removed before cast kicks in, but[code="sql"]select cast('1 234' as int);[/code]will never fly simply because it is not a valid way to represent the number in every locale (though it is valid in some of them).[code="sql"]select isnumeric('1234 ') trailing_yep, isnumeric('1 234') middle_nope;[/code]returns [code="sql"]trailing_yep middle_nope------------ -----------1 0[/code]in the environment I use.Oleg

RE: More fun with ISNUMERIC()

UMG Developer — Tue, 06 Jul 2010 11:13:47 GMT

Nice questions thanks! Yet more reasons to not use ISNUMERIC for most things.

RE: More fun with ISNUMERIC()

Toreador — Tue, 06 Jul 2010 10:17:06 GMT

[quote][b]Oleg Netchaev (7/6/2010)[/b][hr]I think what is happening here is the following: isnumeric('') = 0 and because the trailing spaces are truncated when the strings are compared, i.e. '' = ' ' = ' ' etc, the isnumeric(char(32)) evaluates to isnumeric('') which is known to return 0. This means that isnumeric('any_number_of_spaces_here') is equal to 0 because the trailing spaces are first removed. char(160) is not considered a space (so it is not removed before isnumeric kicks in) though it prints identically with one. Oleg[/quote]That doesn't explain why isnumeric('1 234') = 0, despite being in the standard format for any locale with a space as the thousands separator!

RE: More fun with ISNUMERIC()

Michael Poppers — Tue, 06 Jul 2010 10:13:56 GMT

[quote][b]paul.knibbs (7/5/2010)[/b][hr]I was expecting an answer of 14 (numbers 0-9, +, -, . and $), but of course that wasn't an option, so I had to go and run the script to find out where I was wrong. I can see why , counts as numeric, but some of the other values that count as such are a bit baffling--characters 9, 11, and 12, for instance![/quote]Agreed. I chose 16, figuring I must have missed a decimal- or currency-related symbol. Certainly didn't expect 9-13 to show up as "numeric"s! Thanks to the questioner for the QotD.

RE: More fun with ISNUMERIC()

Oleg Netchaev — Tue, 06 Jul 2010 09:58:49 GMT

This is a very good question, thank you Ron. It really took some time to figure out correct answer. From your explanation:[quote]Odd that running ISNUMERIC() against a space (ASCII 32) returns 0, but a non-breaking space (ASCII 160) returns a 1, eh? [/quote]I think what is happening here is the following: isnumeric('') = 0 and because the trailing spaces are truncated when the strings are compared, i.e. '' = ' ' = ' ' etc, the isnumeric(char(32)) evaluates to isnumeric('') which is known to return 0. This means that isnumeric('any_number_of_spaces_here') is equal to 0 because the trailing spaces are first removed. char(160) is not considered a space (so it is not removed before isnumeric kicks in) though it prints identically with one. Oleg

RE: More fun with ISNUMERIC()

Duncan Pryde — Tue, 06 Jul 2010 00:35:40 GMT

[quote][b]Kari Suresh (7/5/2010)[/b][hr][p]Thanks Duncan. You are right, master DB is in compatibility level 80 and other databases are in compatibility level 90.[/p][/quote]No problem. Glad to help.Duncan

RE: More fun with ISNUMERIC()

Duncan Pryde — Tue, 06 Jul 2010 00:34:30 GMT

[quote][b]savosin_sergey (7/5/2010)[/b][hr]Thank you, Duncan, you've right - my tempdb at compatibility_level=90 and the query from task returns 21, though my test database has compatibility_level=80[/quote]And thank you for noticing the behaviour in the first place!Duncan

RE: More fun with ISNUMERIC()

Kari Suresh — Mon, 05 Jul 2010 23:41:08 GMT

[quote]As I mentioned above, that's almost certainly because your master database is in compatibility level 80 (SQL 2000), while your other databases are in compatibility level 90 or 100 (SQL 2005 or 2008).Do check and let me know.[/quote][p]Thanks Duncan. You are right, master DB is in compatibility level 80 and other databases are in compatibility level 90.[/p]

RE: More fun with ISNUMERIC()

savosin_sergey — Mon, 05 Jul 2010 22:34:28 GMT

[quote][b]Duncan Pryde (7/5/2010)[/b][hr]Looks like a compatibility level issue. If you set the database to SQL Server 2000 compatibility mode, you lose the backslash character (92) from the list. Something to do with it being a valid currency character only in Japan/Korea for SQL 2000 but for all countries in SQL 2005-8.See about half way down [url=http://msdn.microsoft.com/en-us/library/bb510680.aspx][u]this page[/u][/url]Duncan[/quote]Thank you, Duncan, you've right - my tempdb at compatibility_level=90 and the query from task returns 21, though my test database has compatibility_level=80

RE: More fun with ISNUMERIC()

Dietmar Weickert — Mon, 05 Jul 2010 12:18:33 GMT

BOL simply state [quote]ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see Using Monetary Data.[/quote] Honestly, I am used to better quality of Microsoft's product manuals. Documentation of this kind should be left to Oracle.;-)

RE: More fun with ISNUMERIC()

JediSQL — Mon, 05 Jul 2010 09:38:10 GMT

OK, I looked up an [url=http://www.asciitable.com/]ASCII table[/url], and characters 11 & 12 are supposed to be white space type characters. How many people are finding SSMS 2008 printing the two interesting characters I got "♂(11), ♀(12),"?

RE: More fun with ISNUMERIC()

JediSQL — Mon, 05 Jul 2010 09:16:06 GMT

I got the 43 - 57 stuff, and expected some white space, but what's with 11 & 12??? Do they go with the monetary data types because some people are willing to pay for them? :w00t:They are not described in BOL.

RE: More fun with ISNUMERIC()

Duncan Pryde — Mon, 05 Jul 2010 08:13:22 GMT

[quote][b]Kari Suresh (7/5/2010)[/b][hr][p]This query returns count as 20 when executing against "master" DB (excluding '\' ASCII character number 92) and returns 21 when executing with other databases.[/p][/quote]As I mentioned above, that's almost certainly because your master database is in compatibility level 80 (SQL 2000), while your other databases are in compatibility level 90 or 100 (SQL 2005 or 2008).Do check and let me know.Duncan

RE: More fun with ISNUMERIC()

Kari Suresh — Mon, 05 Jul 2010 07:53:39 GMT

[p]This query returns count as 20 when executing against "master" DB (excluding '\' ASCII character number 92) and returns 21 when executing with other databases.[/p]

RE: More fun with ISNUMERIC()

Duncan Pryde — Mon, 05 Jul 2010 05:22:55 GMT

Great question by the way, thanks!Duncan

RE: More fun with ISNUMERIC()

Duncan Pryde — Mon, 05 Jul 2010 05:20:40 GMT

[quote][b]savosin_sergey (7/5/2010)[/b][hr]My Count is only 20 (while right answer is 21)! Here is text result of the query from Question:[code="plain"]Count String----------- -----------20 (9), (10), (11), (12), (13), $(36), +(43), ,(44), -(45), .(46), 0(48), 1(49), 2(50), 3(51), 4(52), 5(53), 6(54), 7(55), 8(56), 9(57), [/code](1 row(s) affected)[/quote]Looks like a compatibility level issue. If you set the database to SQL Server 2000 compatibility mode, you lose the backslash character (92) from the list. Something to do with it being a valid currency character only in Japan/Korea for SQL 2000 but for all countries in SQL 2005-8.See about half way down [url=http://msdn.microsoft.com/en-us/library/bb510680.aspx][u]this page[/u][/url]Duncan

RE: More fun with ISNUMERIC()

paul.knibbs — Mon, 05 Jul 2010 03:30:08 GMT

I was expecting an answer of 14 (numbers 0-9, +, -, . and $), but of course that wasn't an option, so I had to go and run the script to find out where I was wrong. I can see why , counts as numeric, but some of the other values that count as such are a bit baffling--characters 9, 11, and 12, for instance!

RE: More fun with ISNUMERIC()

Toreador — Mon, 05 Jul 2010 02:35:29 GMT

I've just changed my Regional Settings to French, so the thousands seperator is a space. But even now, IsNumeric('1 234') returns 0.I knew there was a good reason that I avoid this function!

RE: More fun with ISNUMERIC()

savosin_sergey — Mon, 05 Jul 2010 00:25:07 GMT

My Count is only 20 (while right answer is 21)! Here is text result of the query from Question:[code="plain"]Count String----------- -----------20 (9), (10), (11), (12), (13), $(36), +(43), ,(44), -(45), .(46), 0(48), 1(49), 2(50), 3(51), 4(52), 5(53), 6(54), 7(55), 8(56), 9(57), [/code](1 row(s) affected)

RE: More fun with ISNUMERIC()

ma-516002 — Mon, 05 Jul 2010 00:15:55 GMT

Doesn't explain why chr(13) is numeric... what function. Nice question.

RE: More fun with ISNUMERIC()

Nakul Vachhrajani — Sun, 04 Jul 2010 03:46:29 GMT

Hello!For those who are interested in an official documentation on this behaviour, refer BOL at: http://msdn.microsoft.com/en-us/library/ms186272.aspxThank-you!

More fun with ISNUMERIC()

ronmoses — Sat, 03 Jul 2010 14:25:11 GMT

Comments posted to this topic are about the item [B]More fun with ISNUMERIC()[/B]