July 3, 2010 at 2:25 pm
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
July 4, 2010 at 3:46 am
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
July 5, 2010 at 12:15 am
Doesn't explain why chr(13) is numeric... what function. Nice question.
July 5, 2010 at 12:25 am
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)
July 5, 2010 at 2:35 am
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!
July 5, 2010 at 3:30 am
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!
July 5, 2010 at 5:20 am
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
July 5, 2010 at 5:22 am
Great question by the way, thanks!
Duncan
July 5, 2010 at 7:53 am
[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
July 5, 2010 at 8:13 am
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
July 5, 2010 at 9:16 am
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
July 5, 2010 at 9:38 am
July 5, 2010 at 12:18 pm
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.
July 5, 2010 at 10:34 pm
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
July 5, 2010 at 11:41 pm
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
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply