More fun with ISNUMERIC()

Question

More fun with ISNUMERIC()

ronmoses@gmail.com

SSCarpal Tunnel

Points: 4480
More actions
July 3, 2010 at 2:25 pm

#387271

Comments posted to this topic are about the item More fun with ISNUMERIC()
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 1

Hello!

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

Thank-you!

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

ma-516002 Hall of Fame Points: 3359 More actions · Answer 2

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

savosin_sergey SSC Enthusiast Points: 106 More actions · Answer 3

My Count is only 20 (while right answer is 21)! Here is text result of the query from Question:

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

(1 row(s) affected)

Toreador SSChampion Points: 11366 More actions · Answer 4

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!

paul.knibbs SSCoach Points: 15320 More actions · Answer 5

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!

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 6

savosin_sergey (7/5/2010)
My Count is only 20 (while right answer is 21)! Here is text result of the query from Question:
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),
(1 row(s) affected)

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 this page

Duncan

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 7

Duncan Pryde

SSCertifiable

Points: 7956

July 5, 2010 at 5:22 am

#1188736

Great question by the way, thanks!

Duncan

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 8

[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]

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 9

Kari Suresh (7/5/2010)
[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]

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

JediSQL SSCrazy Points: 2118 More actions · Answer 10

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.

Sincerely,
Daniel

JediSQL SSCrazy Points: 2118 More actions · Answer 11

OK, I looked up an 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),"?

Sincerely,
Daniel

Dietmar Weickert SSCrazy Points: 2398 More actions · Answer 12

BOL simply state

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.

Honestly, I am used to better quality of Microsoft's product manuals. Documentation of this kind should be left to Oracle.;-)

Best regards,
Dietmar Weickert.

savosin_sergey SSC Enthusiast Points: 106 More actions · Answer 13

Duncan Pryde (7/5/2010)
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 this page
Duncan

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

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 14

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.

[p]Thanks Duncan. You are right, master DB is in compatibility level 80 and other databases are in compatibility level 90.[/p]

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha