Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

More fun with ISNUMERIC() Expand / Collapse
Author
Message
Posted Tuesday, July 06, 2010 11:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:50 AM
Points: 2,163, Visits: 2,184
Nice questions thanks! Yet more reasons to not use ISNUMERIC for most things.
Post #948061
Posted Tuesday, July 06, 2010 11:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:53 PM
Points: 1,676, Visits: 1,744
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
Post #948067
Posted Tuesday, July 06, 2010 12:32 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:29 PM
Points: 3,924, Visits: 1,586
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.
Post #948120
Posted Tuesday, July 06, 2010 4:08 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 07, 2010 12:55 AM
Points: 771, Visits: 504
Excellent question. Thanks.

Also, thanks to Oleg and Duncan for their explanations.
Post #948227
Posted Wednesday, July 07, 2010 12:30 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:13 AM
Points: 1,861, Visits: 368
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..
Post #948324
Posted Wednesday, July 07, 2010 2:14 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 1,656, Visits: 5,998
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?
Post #948377
Posted Wednesday, July 07, 2010 1:42 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 4:26 PM
Points: 582, Visits: 451
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.
Post #948812
Posted Wednesday, July 07, 2010 2:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:28 PM
Points: 20,453, Visits: 14,063
thanks for the QOD



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #948857
Posted Wednesday, July 07, 2010 6:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 35,944, Visits: 30,229
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #948926
Posted Wednesday, July 07, 2010 9:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 07, 2014 5:39 PM
Points: 18, Visits: 92
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
Post #948977
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse