More fun with ISNUMERIC()

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ron Moses » More fun with ISNUMERIC()

32 posts, Page 1 of 4

1 2 3 4 »»»

More fun with ISNUMERIC()

Rate Topic

Display Mode

Topic Options

Author

Message

ronmoses

Posted Saturday, July 03, 2010 2:25 PM

Say Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 5:48 AM
Points: 661, Visits: 698

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

Nakul Vachhrajani

Posted Sunday, July 04, 2010 3:46 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, June 10, 2013 6:56 AM
Points: 1,282, Visits: 1,613

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://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @nakulv_sql
Google Plus: +Nakul

Post #947306

ma-516002

Posted Monday, July 05, 2010 12:15 AM

SSCommitted

Group: General Forum Members
Last Login: Monday, May 06, 2013 3:27 AM
Points: 1,864, Visits: 297

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

Post #947383

savosin_sergey

Posted Monday, July 05, 2010 12:25 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 24, 2013 8:00 AM
Points: 77, Visits: 34

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

Toreador

Posted Monday, July 05, 2010 2:35 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 2:39 AM
Points: 1,384, Visits: 4,874

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

paul.knibbs

Posted Monday, July 05, 2010 3:30 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:04 AM
Points: 1,270, Visits: 4,306

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

Duncan Pryde

Posted Monday, July 05, 2010 5:20 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, June 14, 2013 2:30 AM
Points: 3,063, Visits: 1,335

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

Duncan Pryde

Posted Monday, July 05, 2010 5:22 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, June 14, 2013 2:30 AM
Points: 3,063, Visits: 1,335

Great question by the way, thanks!

Duncan

Post #947472

Kari Suresh

Posted Monday, July 05, 2010 7:53 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 01, 2013 11:12 PM
Points: 248, Visits: 372

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

Duncan Pryde

Posted Monday, July 05, 2010 8:13 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, June 14, 2013 2:30 AM
Points: 3,063, Visits: 1,335

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 »

32 posts, Page 1 of 4

1 2 3 4 »»»

Permissions