Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

More fun with ISNUMERIC() Expand / Collapse
Author
Message
Posted Saturday, July 3, 2010 2:25 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 18, 2014 6:41 AM
Points: 907, Visits: 892
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
Post #947263
Posted Sunday, July 4, 2010 3:46 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 10:22 PM
Points: 1,457, Visits: 1,858
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
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #947306
Posted Monday, July 5, 2010 12:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 12:34 AM
Points: 2,322, Visits: 318
Doesn't explain why chr(13) is numeric... what function. Nice question.
Post #947383
Posted Monday, July 5, 2010 12:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 4, 2014 12:41 AM
Points: 77, Visits: 37
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)
Post #947384
Posted Monday, July 5, 2010 2:35 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 1,832, Visits: 6,650
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!
Post #947406
Posted Monday, July 5, 2010 3:30 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 12, 2014 3:32 AM
Points: 1,639, Visits: 5,721
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!
Post #947434
Posted Monday, July 5, 2010 5:20 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:31 AM
Points: 3,352, Visits: 1,490
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
Post #947471
Posted Monday, July 5, 2010 5:22 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:31 AM
Points: 3,352, Visits: 1,490
Great question by the way, thanks!

Duncan
Post #947472
Posted Monday, July 5, 2010 7:53 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 22, 2013 1:05 AM
Points: 248, Visits: 373

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



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
Post #947520
Posted Monday, July 5, 2010 8:13 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:31 AM
Points: 3,352, Visits: 1,490
Kari Suresh (7/5/2010)

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



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
Post #947527
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse