ISNUMERIC() bug?

Question

Post reply

ISNUMERIC() bug?

Viewing 6 posts - 16 through 20 (of 20 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

>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.
"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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

>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.
"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)
Intro to Tally Tables and Functions

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

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/[/url]

grantsaunders75 Valued Member Points: 63 More actions · Answer 4

grantsaunders75

Valued Member

Points: 63

July 2, 2014 at 6:39 pm

#1726667

ISNUMERIC('Value'+'e01') -- always worked perfectly for me.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

grantsaunders75 (7/2/2014)

ISNUMERIC('Value'+'e01') -- always worked perfectly for me.

Sneaky 😀

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

Nadrek SSC-Insane Points: 20039 More actions · Answer 6

Here's a slightly longer set of test data.

Note that spaces are particularly interesting, as are the spelled out FLOAT limits.

Please remove the TRY_CONVERT blocks for SQL Server versions below 2012.



SELECT

  NumberString,

  [ISNUMERIC] = ISNUMERIC(NumberString),

  [ISNUMERIC extra Grant] = ISNUMERIC(NumberString+'e01'),

  [ISNUMERIC extra Grant Chris] = ISNUMERIC(NumberString+'e00'),

  [MONEY] = TRY_CONVERT(MONEY, NumberString),

  [BIGINT] = TRY_CONVERT(BIGINT, NumberString),

  [INT] = TRY_CONVERT(INT, NumberString),

  [SMALLINT] = TRY_CONVERT(SMALLINT, NumberString),

  [TINYINT] = TRY_CONVERT(TINYINT, NumberString),

  [DECIMAL(38,36)] = TRY_CONVERT(DECIMAL(38,36), NumberString),

  [DECIMAL(38,0)] = TRY_CONVERT(DECIMAL(38,0), NumberString),

  [REAL] = TRY_CONVERT(REAL, NumberString),

  [FLOAT] = TRY_CONVERT(FLOAT, NumberString),

  [BIT] = TRY_CONVERT(BIT, NumberString)

FROM (VALUES

  ('999999999999999999999999999999999999999999999999999999'),

  ('-999999999999999999999999999999999999999999999999999999'),

  ('0'),

  ('-0'),

  ('1'),

  ('-1'),

  ('2'),

  ('-2'),

  ('00000'),

  ('-00000'),

  ('00000.0000'),

  ('-00000.0000'),

  ('9223372036854775807'),

  ('-9223372036854775807'),

  ('9223372036854775808'),

  ('-9223372036854775808'),

  ('9223372036854775809'),

  ('-9223372036854775809'),

  ('2147483647'),

  ('-2147483647'),

  ('2147483648'),

  ('-2147483648'),

  ('2147483649'),

  ('-2147483649'),

  ('32767'),

  ('-32767'),

  ('32768'),

  ('-32768'),

  ('32769'),

  ('-32769'),

  ('255'),

  ('-255'),

  ('256'),

  ('-256'),

  ('257'),

  ('-257'),

  ('12.3'),

  ('-12.3'),

  ('0.000000000000000000000000000000000001'),

  ('-0.000000000000000000000000000000000001'),

  ('.000000000000000000000000000000000001'),

  ('-.000000000000000000000000000000000001'),

  (NULL),

  (''),

  (' '),

  ('   '),

  ('1d1'),

  ('-1d1'),

  ('1e1'),

  ('-1e1'),

  ('999999999999999999999999999999999999999999999999999999e1'),

  ('-999999999999999999999999999999999999999999999999999999e1'),

  ('34000000000000000000000000000000000000'),

  ('-34000000000000000000000000000000000000'),

  ('3.4e37'),

  ('-3.4e37'),

  ('340000000000000000000000000000000000000'),

  ('-340000000000000000000000000000000000000'),

  ('3.4e38'),

  ('-3.4e38'),

  ('3400000000000000000000000000000000000000'),

  ('-3400000000000000000000000000000000000000'),

  ('3.4e39'),

  ('-3.4e39'),

  ('17900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'),

  ('-17900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'),

  ('1.7899999e307'),

  ('-1.7899999e307'),

  ('179000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'),

  ('-179000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'),

  ('1.7899999e308'),

  ('-1.7899999e308'),

  ('1790000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'),

  ('-1790000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'),

  ('1.7899999e309'),

  ('-1.7899999e309'),

  ('1.79e307'),

  ('-1.79e307'),

  ('1.79e308'),

  ('-1.79e308'),

  ('1.79e309'),

  ('-1.79e309'),

  ('1.790001e307'),

  ('-1.790001e307'),

  ('1.790001e308'),

  ('-1.790001e308'),

  ('1.790001e309'),

  ('-1.790001e309'),

  ('1.799001e307'),

  ('-1.799001e307'),

  ('1.799001e308'),

  ('-1.799001e308'),

  ('1.799001e309'),

  ('-1.799001e309'),

  ('$'),

  ('$1.0'),

  ('-$1.0'),

  ('$-1.0'),

  ('1.1e1'),

  ('-1.1e1'),

  ('.'),

  (','),

  ('2012-01-01'),

  ('01-01-2012'),

  ('1-1-12'),

  ('1-12'),

  ('2012.01.01'),

  ('01.01.2012'),

  ('1.1.12'),

  ('1.12'),

  ('e'),

  ('d'),

  ('-'),

  ('1d1d1'),

  ('-1d1d1'),

  ('1e1e1'),

  ('-1e1e1'),

  ('1e1d1'),

  ('-1e1d1'),

  ('1d1e1'),

  ('-1d1e1')

) d (NumberString)