More fun with ISNUMERIC()

Question

More fun with ISNUMERIC()

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 1

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 SSCertifiable Points: 7956 More actions · Answer 2

Kari Suresh (7/5/2010)
[p]Thanks Duncan. You are right, master DB is in compatibility level 80 and other databases are in compatibility level 90.[/p]

No problem. Glad to help.

Duncan

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 3

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 SSCrazy Points: 2151 More actions · Answer 4

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 SSChampion Points: 11368 More actions · Answer 5

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!

UMG Developer SSChampion Points: 13482 More actions · Answer 6

Nice questions thanks! Yet more reasons to not use ISNUMERIC for most things.

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 7

Toreador (7/6/2010)
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!

What I mentioned in my post was related only to the trailing spaces behavior. In other words

select cast('1234 ' as int);

is fine because the trailing spaces are removed before cast kicks in, but

select cast('1 234' as int);

will never fly simply because it is not a valid way to represent the number in every locale (though it is valid in some of them).

select

isnumeric('1234 ') trailing_yep,

isnumeric('1 234') middle_nope;

returns

trailing_yep middle_nope

------------ -----------

1 0

in the environment I use.

Oleg

SanjayAttray SSChampion Points: 13157 More actions · Answer 8

different answer for different sql versions.

SQL 2005 and 2008 gives 21 while 2000 with nvarchar(8000) throws error

Msg 2717, Level 16, State 2, Line 1

The size (8000) given to the parameter '@String' exceeds the maximum allowed (4000).

Parameter '@String' has an invalid data type.

giving varchar(8000) would give 20 answer.

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),

Thanks for a good question.

SQL DBA.

KevinC. SSCommitted Points: 1570 More actions · Answer 9

Excellent question. Thanks.

Also, thanks to Oleg and Duncan for their explanations.

ziangij SSCrazy Eights Points: 8066 More actions · Answer 10

thanks Duncan 🙂

I also had the same issue... was getting the count 20 but didn't know why... its because of the compatibility level set to 80..

Toreador SSChampion Points: 11368 More actions · Answer 11

Oleg Netchaev (7/6/2010)
select cast('1 234' as int);
will never fly simply because it is not a valid way to represent the number in every locale (though it is valid in some of them).

that's the bit I can't understand. Does the same not apply to

isnumeric('1.234,56')

yet this returns 1?

Olga B SSCommitted Points: 1529 More actions · Answer 12

Toreador (7/7/2010)
that's the bit I can't understand. Does the same not apply to
isnumeric('1.234,56')
yet this returns 1?

I wonder if it's because ISNUMERIC disregards the position of the comma? For instance,

isnumeric('123,45.7')

returns 1

As does

isnumeric('12345.67,89')

I also noticed that converting a string of numbers with a comma anywhere in it (e.g., '12,345') to a numeric value results in an error, even though ISNUMERIC returns 1 on that string.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

thanks for the QOD

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 14

Olga B (7/7/2010)
I also noticed that converting a string of numbers with a comma anywhere in it (e.g., '12,345') to a numeric value results in an error, even though ISNUMERIC returns 1 on that string.

Heh... "It depends"... 😉

SELECT ISNUMERIC('1,2,3,4,5,6,7,8,9'),

CAST('1,2,3,4,5,6,7,8,9' AS MONEY)

By definition, ISNUMERIC will return a 1 if the operand can be converted to ANY numeric value using ANY numeric datatype conversion... not just the ones you expect.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pparsons Old Hand Points: 397 More actions · Answer 15

I'd be curious to see what the expected result is for the 21 count. I use SQL2K and long ago ended up creating my own IsNumeric function because of the issues with the native version, so assumed based on the comma issue the "right" answer was most likely 21 (comma is treated differently in SQL2K vs. SQL2K5). I read mention of the character 92, which was weird to me, and with the other variants on answers it only goes to show (IMO) the uselessness of this function