SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


More fun with ISNUMERIC()


More fun with ISNUMERIC()

Author
Message
JediSQL
JediSQL
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 189
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
JediSQL
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 189
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
Dietmar Weickert
Dietmar Weickert
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1622 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.
savosin_sergey
savosin_sergey
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
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
Kari Suresh
Kari Suresh
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 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
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3604 Visits: 1552
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
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3604 Visits: 1552
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
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1795 Visits: 1814
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
Michael Poppers
Michael Poppers
SSC Eights!
SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)SSC Eights! (835 reputation)

Group: General Forum Members
Points: 835 Visits: 416
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.
Toreador
Toreador
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2815 Visits: 8084
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search