ISNUMERIC() bug?
-
>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) -
>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) -
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] -
ISNUMERIC('Value'+'e01') -- always worked perfectly for me.
-
grantsaunders75 (7/2/2014)
ISNUMERIC('Value'+'e01') -- always worked perfectly for me.Sneaky 😀
SELECTNumberString,
[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 ShawFor 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 -
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)
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply