Why doesn’t ISNUMERIC work correctly? (SQL Spackle)

Question

Post reply

Why doesn’t ISNUMERIC work correctly? (SQL Spackle)

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

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

wagner crivelini SSC Eights! Points: 922 More actions · Answer 1

Good catch... I've never thought about it.

Great article. Short and perfectly clear.

Thank you!

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 2

Hi Jeff,

Its very nice . Thanks a lot. 🙂

Thanks & Regards,
MC

steven.malone Say Hey Kid Points: 688 More actions · Answer 3

First off thanks, like others I had not stopped to think about "d" and "e" and I am a little surprised "x" was not included if they were. go figure.

However, when I ran your script on my machine the returned data set included 92 \ (backslash).

Is there some setting that affects that?

I expanded your script a little:

--===== Return all characters that ISNUMERIC thinks is numeric

-- (uses values 0-255 from the undocumented spt_Values table

-- instead of a loop from 0-255)

SELECT [Ascii Code] = STR(Number),

[Ascii Character] = CHAR(Number),

[Standalone] = ISNUMERIC(CHAR(Number)),

[CharZero] = ISNUMERIC(CHAR(Number)+'0'),

[ZeroCharZero] = ISNUMERIC('0'+CHAR(Number)+'0'),

[ZeroCharCharZero] = ISNUMERIC('0'+CHAR(Number)+CHAR(Number)+'0'),

[ZeroChar] = ISNUMERIC('0'+CHAR(Number))

FROM Master.dbo.spt_Values

WHERE Type = 'P'

AND Number BETWEEN 0 AND 255

AND (ISNUMERIC(CHAR(Number)) = 1

or 1 = ISNUMERIC(CHAR(Number)+'0')

or 1 = ISNUMERIC('0'+CHAR(Number)+'0')

or 1 = ISNUMERIC('0'+CHAR(Number))

)

And got this result set:

Ascii Code Character Standalone CharZero ZeroCharZero ZeroCharCharZero ZeroChar

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

0 0 0 1 1 1

9 1 1 0 0 0

10 1 1 0 0 0

11 1 0 0 0 1

12 1 0 0 0 1

13 1 1 0 0 0

32 0 1 0 0 1

36 $ 1 1 0 0 0

43 + 1 1 0 0 0

44 , 1 1 1 1 1

45 - 1 1 0 0 0

46 . 1 1 1 0 1

48 0 1 1 1 1 1

49 1 1 1 1 1 1

50 2 1 1 1 1 1

51 3 1 1 1 1 1

52 4 1 1 1 1 1

53 5 1 1 1 1 1

54 6 1 1 1 1 1

55 7 1 1 1 1 1

56 8 1 1 1 1 1

57 9 1 1 1 1 1

68 D 0 0 1 0 0

69 E 0 0 1 0 0

92 \ 1 1 0 0 0

100 d 0 0 1 0 0

101 e 0 0 1 0 0

128 € 1 1 0 0 0

160 1 0 0 0 1

162 ¢ 1 1 0 0 0

163 £ 1 1 0 0 0

164 ¤ 1 1 0 0 0

165 ¥ 1 1 0 0 0

Notice that 44 (comma) has a one in the ZeroCharCharZero column.

Also that 0 has ones in the last three columns.

I tried and select convert(numeric,'0,,0')

go

select convert(numeric,'0'+char(0)+'0')

go

select convert(numeric,'0'+char(0)+char(0)+'0')

go

select convert(numeric,'0'+char(0))

go

all throw a errors

Bottom line is from now on if I need to validate input I think I will put in a try block assigning the string to a variable of the actual type I need and if I need to validate columns use a type specific function.

Again, thanks for the food for thougt.

svetlana.davkovska Valued Member Points: 65 More actions · Answer 4

This function is OK for integer numbers, what about decimal or negative ?

crashdan SSCommitted Points: 1990 More actions · Answer 5

crashdan

SSCommitted

Points: 1990

December 1, 2010 at 5:53 am

#1257270

please also consider 0E0 and numbers like them.

Brandie Tarvin SSC Guru Points: 172995 More actions · Answer 6

svetlana.davkovska (12/1/2010)
This function is OK for integer numbers, what about decimal or negative ?

I'm not sure I understand your question.

If you test it, ISNUMERIC() will come out as true for both. Looking at Jeff's chart, the negative sign and the period are both registering as legitimate "numeric" values because they are both used frequently in currencies, absolute, and non-absolute numbers.

select ISNUMERIC(-5) as Neg5, ISNUMERIC(5.55) as Dec5

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

BobMcClellan SSC-Addicted Points: 400 More actions · Answer 7

Excellent post Jeff. This is my first exposure to a "Spackle" post. This short, straight forward example, explanation and solution of a common yet not

so apparent problem is great. Reading one of these a day would have to make me a better at programmer. Thanks for the post Jeff!

Bob McClellan.

LadyReader Ten Centuries Points: 1192 More actions · Answer 8

This was a good demo and it taught me something I didn't know. Minor error: your end point for the code for the digits is incorrect:

"Ascii Codes 48 thru 59 are included because they represent the digits 0 through 9" --> should be 48 thru 57.

yuri 38568 SSC Rookie Points: 35 More actions · Answer 9

My 2 cents: http://codecorner.galanter.net/2009/04/03/tsql-isnumeric-function-returns-false-positives/[/url]

A UDF to determine if passed expression is a number indeed.

James Stephens Mr or Mrs. 500 Points: 517 More actions · Answer 10

Jeff,

Thanks for illuminating this. I had posted a question/beef about this in Tek-Tips a few months ago and judging from from the replies, I got the impression that it was I who was crazy and not SQL server's IsNumeric() function.

--Jim

chrisn-585491 SSCoach Points: 16006 More actions · Answer 11

Very good article Jeff!

As a side note I'm always having to explain to clients and coworkers that phone "numbers" and account "numbers", etc are character fields, not numeric fields...

wayne-862477 SSC Rookie Points: 25 More actions · Answer 12

The problem is that "IsNumeric" does not validate for something that will go correctly into an int or decimal field -- which is a problem throughout Microsoft's code. I usually find myself validating this data well before it hits SQL because of this inconsistency. Realistically the best thing to do would be to create validation by type (IsInt, IsDecimal etc) so you also don't run into overflow problems and things like that.

Slick84 SSCertifiable Points: 5602 More actions · Answer 13

Excellent article.

Thanks for writing about this. And the alternative solutions help in making sense out of all of this.

-

Slick

--
:hehe:

E-DB SSC Rookie Points: 49 More actions · Answer 14

wayne-862477 (12/1/2010)
The problem is that "IsNumeric" does not validate for something that will go correctly into an int or decimal field -- which is a problem throughout Microsoft's code. I usually find myself validating this data well before it hits SQL because of this inconsistency. Realistically the best thing to do would be to create validation by type (IsInt, IsDecimal etc) so you also don't run into overflow problems and things like that.

Exactly. If I'm loading into a numeric field from a varchar field, I would expect to have a function that validates whether the value will fit into the numeric field.

This would be less of a problem if UDFs had better performance, but alas we're stuck with a bunch of hacks to validate data.

WayneS SSC Guru Points: 95409 More actions · Answer 15

I have a question for clarification:

Ascii Codes 36 (Dollar sign), 128 (Euro sign), 163 (British Pound sign), and 164 (Yen sign) are included because they are frequently used as enumerators to identify the type of number or, in this case, the currency type the number is meant to represent.

Ascii Code 164 is a special character and is included because it is frequently used by accountants and some software to indicate a total or subtotal of some type. It is also used by some to indicate they don't know what the enumerator is.

Is ASCII 164 the Yen (1st quote), or something else (2nd quote)? It does appear to be the Yen, and you didn't cover ASCII 165, so in the second quote should that be 165?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2