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


ISNUMERIC() bug?


ISNUMERIC() bug?

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)

Group: General Forum Members
Points: 848787 Visits: 46700

Sure, one complete explanation coming right up... but I'm a bit surprised you didn't figure it out yourself... remember, you asked the question...

First, I modified your code so we could see the actual character being represented instead of just the ASCII numeric representation....

declare @i integer
set @i = 0
while @i < 256
begin
print N'Ascii code : ' + CAST ( @i as varchar) +' '+ CHAR(@I)
+ ' isnumeric : ' + cast (isnumeric(char(@i)) as varchar)
set @i = @i + 1
end

Here's the result set which has been abbreviated as a readability-courtesy to show only those single characters that are considered to be numeric...

Ascii code : 9 isnumeric : 1
Ascii code : 10
isnumeric : 1
Ascii code : 11 isnumeric : 1
Ascii code : 12 isnumeric : 1
Ascii code : 13
isnumeric : 1
Ascii code : 36 $ isnumeric : 1
Ascii code : 43 + isnumeric : 1
Ascii code : 44 , isnumeric : 1
Ascii code : 45 - isnumeric : 1
Ascii code : 46 . isnumeric : 1
Ascii code : 48 0 isnumeric : 1
Ascii code : 49 1 isnumeric : 1
Ascii code : 50 2 isnumeric : 1
Ascii code : 51 3 isnumeric : 1
Ascii code : 52 4 isnumeric : 1
Ascii code : 53 5 isnumeric : 1
Ascii code : 54 6 isnumeric : 1
Ascii code : 55 7 isnumeric : 1
Ascii code : 56 8 isnumeric : 1
Ascii code : 57 9 isnumeric : 1
Ascii code : 128 € isnumeric : 1
Ascii code : 160 isnumeric : 1
Ascii code : 163 £ isnumeric : 1
Ascii code : 164 ¤ isnumeric : 1
Ascii code : 165 ¥ isnumeric : 1

Ascii 9 is a TAB character and is included because a column of numbers is frequently delimited by a TAB.

Ascii 10 is a Line Feed character and is included because the last column of numbers is frequently terminated by a Line Feed character.

Ascii 11 is a Vertical Tab character and is included because the last column of numbers is frequently terminated by a Vertical Tab character.

Ascii 12 is a Form Feed character and is included because the last column numbers of the last row is sometimes terminated by a Form Feed character.

Ascii 13 is a Carriage Return character and is included because the last column of numbers is frequently terminated by a Carriage Return character.

Ascii 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 43 (Plus sign), 44 (Comma), 45 (Minus sign), and 46 (Decimal place) are included because they are frequently included in numeric columns to mark where on the number line the number appears and for simple formatting.

Ascii 160 is a special "hard space" and is included because it is frequently used to left pad numeric columns so the column of numbers appears to be right justified. Ascii 32 is a "soft space" and is not included because a single space does not usually represent a column of numbers. Ascii 32 is, however, a valid numeric character when used to create right justified numbers as is Ascii 160 but a single Ascii 32 character is NOT numeric. In fact, a string of Ascii 32 spaces is not considered to be numeric but a string of spaces with even a single digit in it is considered to be numeric.

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

Ascii 48-59 are included because they represent the digits 0 through 9.

Do notice that the "e" , "d" and (everybody forgot about this) "x" are not included as numeric in the results because a single "e", "d", or "x" is NOT considered to be numeric. Two of these letters are for two different forms of scientific notation and the "x" indicates a hexidecimal output in SQL. Further, when properly formatted, the letters "a-f" are also considered to be numeric and are also not included in the list of single characters. All of these "unlisted" characters are highly positional in nature. That is, they must be in the correct position in association with numeric digits and other numeric symbology to be considered to be numeric. Depending on where they appear in a column of numbers determines if and when they will be treated as numeric or not.

ISNUMERIC is NOT and should never be treated as ISALLDIGITS. It was never intended to mean that. Use the NOT LIKE '%[^0-9]%' regular expression to determine if something ISALLDIGITS... If you try to use ISNUMERIC for that, then you're using it the wrong way.

Any more questions on ISNUMERIC?



--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
detzu
detzu
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 6

Thank you for the work ! It's really helpfull .

Why can not select cast ( '123'+char(160) as int) work ?

Seems to be a difference of coding between this 2 functions. ISNUMERIC working differenty as cast is not a good idea.

Again thank you for the detailed explanation.


Frank Kalis
Frank Kalis
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72511 Visits: 289

You might want to check out my reply here: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=202581 CHAR(160) is a non-breakable space and as such considered a "noise" character in SQL Server. At least CAST treats it this way. ISNUMERIC however treats this character as thousands separator.

ISNUMERIC is probably one of the most useless functions in SQL Server, IMHO. ....at least, when you don't need to rely on this weired behaviour.

Jeff, can you give an example where 'a-f' are considered by ISNUMERIC to be convertable to a number?



--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Vladan
Vladan
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18698 Visits: 783

Interesting thing though... when you CAST/CONVERT to INT, CHAR(160) will raise an error. But if you try CAST/CONVERT to FLOAT, statement will succeed and the nbsp character is ignored, if it is at the beginning of string (not if it is on the right end of string).

select cast(char(160)+'1234' as float)
select convert(float, char(160)+'1234')

Probably, as Jeff said, it is ignored if used to pad the numbers from left. Well... At the same time, select ISNUMERIC(char(160) + '1234') returns 0. As mentioned already several times, ISNUMERIC is rather unpredictable and should be avoided or used with great care : some strings that return ISNUMERIC = 0 can be successfully converted to float.





Don Bryan-238042
Don Bryan-238042
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 1
modify the script to examine the "numeric" characters
declare @i integer
set @i = 0
while @i < 256
begin
if isnumeric(char(@i)) = 1
print N'Ascii code : ' + CAST ( @i as varchar) + ' isnumeric : ' + cast (isnumeric(char(@i)) as varchar) + ' Char : ' + char(@i)
set @i = @i + 1
end

result

Ascii code : 9 isnumeric : 1 Char :
Ascii code : 10 isnumeric : 1 Char :
Ascii code : 11 isnumeric : 1 Char :
Ascii code : 12 isnumeric : 1 Char :
Ascii code : 13 isnumeric : 1 Char :
Ascii code : 36 isnumeric : 1 Char : $
Ascii code : 43 isnumeric : 1 Char : +
Ascii code : 44 isnumeric : 1 Char : ,
Ascii code : 45 isnumeric : 1 Char : -
Ascii code : 46 isnumeric : 1 Char : .
Ascii code : 48 isnumeric : 1 Char : 0
Ascii code : 49 isnumeric : 1 Char : 1
Ascii code : 50 isnumeric : 1 Char : 2
Ascii code : 51 isnumeric : 1 Char : 3
Ascii code : 52 isnumeric : 1 Char : 4
Ascii code : 53 isnumeric : 1 Char : 5
Ascii code : 54 isnumeric : 1 Char : 6
Ascii code : 55 isnumeric : 1 Char : 7
Ascii code : 56 isnumeric : 1 Char : 8
Ascii code : 57 isnumeric : 1 Char : 9
Ascii code : 92 isnumeric : 1 Char : \
Ascii code : 128 isnumeric : 1 Char : €
Ascii code : 160 isnumeric : 1 Char :
Ascii code : 162 isnumeric : 1 Char : ¢
Ascii code : 163 isnumeric : 1 Char : £
Ascii code : 164 isnumeric : 1 Char : ¤
Ascii code : 165 isnumeric : 1 Char : ¥

For the most part the explanation is "valid" punctuation in a numeric "sting". Currency, signs and separators. Can't explain Horizontal Tab(9), Newline(10), Vertical Tab(11), Form Feed (12) and carriage Return (13) other than their presence at the beginning of a numeric "string" does allow the sting to evaluate as numeric.

select isnumeric(char(13)+'123' )

Characters above 127 will be different based on the codepage currently loaded.

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)

Group: General Forum Members
Points: 848787 Visits: 46700

>Why can not select cast ( '123'+char(160) as int) work ?

Probably for the same reason that select cast ( '123'+char(12) as int) won't work. The extra special characters just aren't convertable to INT. SELECT CAST('123,456' AS INT) won't work either. Although the characters pass the ISNUMERIC test, they just won't convert to INT. They will cast to MONEY, however.

Microsoft would have us all believe that it's not a fault, it's a feature

Like I said previously... if you want to know if a string is numeric enough to be CAST as a number, ISNUMERIC is not the test it must pass.



--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)SSC Guru (848K reputation)

Group: General Forum Members
Points: 848787 Visits: 46700

>Jeff, can you give an example where 'a-f' are considered by ISNUMERIC to be convertable to a number?

Sure Frank,

There's others but these will do... only one out of a-f that I can't get to roll to 1 is "f". My mistake on that... ISNUMERIC is really fickle

These all return 1...

SELECT ISNUMERIC(0X0a)
SELECT ISNUMERIC(0X0b)
SELECT ISNUMERIC(0X0c)
SELECT ISNUMERIC(0X0d)
SELECT ISNUMERIC('0d01')
SELECT ISNUMERIC(0X0e)
SELECT ISNUMERIC(1e10)

This returns 0...

SELECT ISNUMERIC(0X0f)



--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Frank Kalis
Frank Kalis
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72511 Visits: 289

Aah, yes, I forgot about these hex thingies which are convertible to INT. One of these strange days, I get following results

SELECT ISNUMERIC(0X0e) AS E, ISNUMERIC(0X0f) AS F

E F
----------- -----------
0 0

(1 row(s) affected)

while

SELECT CAST(0X0e AS INT) AS E, CAST(0X0f AS INT) AS F

E F
----------- -----------
14 15

(1 row(s) affected)

works.



--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
grantsaunders75
grantsaunders75
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 0
ISNUMERIC('Value'+'e01') -- always worked perfectly for me.
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)

Group: General Forum Members
Points: 162751 Visits: 21429
grantsaunders75 (7/2/2014)
ISNUMERIC('Value'+'e01') -- always worked perfectly for me.


Sneaky :-D

SELECT 
NumberString,
[ISNUMERIC] = ISNUMERIC(NumberString),
[ISNUMERIC extra] = ISNUMERIC(NumberString+'e00'),
[Number] = CASE WHEN ISNUMERIC(NumberString+'e00') = 1 THEN CAST(NumberString AS FLOAT) ELSE NULL END
FROM (VALUES
('999999999999999999999999999999999999999999999999999999'),
('1d1'),
('1e1')
) d (NumberString)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
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