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 Monday, July 5, 2010 9:16 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, September 13, 2014 8:22 PM
Points: 47, Visits: 139
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?
They are not described in BOL.


Sincerely,
Daniel
Post #947557
Posted Monday, July 5, 2010 9:38 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, September 13, 2014 8:22 PM
Points: 47, Visits: 139
OK, I looked up an ASCII table, 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
Post #947567
Posted Monday, July 5, 2010 12:18 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:17 AM
Points: 1,608, Visits: 374
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.
Post #947610
Posted Monday, July 5, 2010 10:34 PM
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
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
Post #947692
Posted Monday, July 5, 2010 11:41 PM


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
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.


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



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 #947710
Posted Tuesday, July 6, 2010 12:34 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: Tuesday, September 2, 2014 2:55 AM
Points: 3,352, Visits: 1,482
savosin_sergey (7/5/2010)


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


And thank you for noticing the behaviour in the first place!

Duncan
Post #947723
Posted Tuesday, July 6, 2010 12:35 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: Tuesday, September 2, 2014 2:55 AM
Points: 3,352, Visits: 1,482
Kari Suresh (7/5/2010)


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



No problem. Glad to help.

Duncan
Post #947724
Posted Tuesday, July 6, 2010 9:58 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:21 AM
Points: 1,676, Visits: 1,757
This is a very good question, thank you Ron. It really took some time to figure out correct answer. From your explanation:

Odd that running ISNUMERIC() against a space (ASCII 32) returns 0, but a non-breaking space (ASCII 160) returns a 1, eh?

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

Post #947999
Posted Tuesday, July 6, 2010 10:13 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:58 AM
Points: 654, Visits: 400
paul.knibbs (7/5/2010)
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!

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.
Post #948012
Posted Tuesday, July 6, 2010 10:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:20 AM
Points: 1,740, Visits: 6,366
Oleg Netchaev (7/6/2010)
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



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

Add to briefcase ««1234»»»

Permissions Expand / Collapse