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 6, 2010 11:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
Nice questions thanks! Yet more reasons to not use ISNUMERIC for most things.
Post #948061
Posted Tuesday, July 6, 2010 11:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:58 AM
Points: 1,676, Visits: 1,758
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 6, 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: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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 6, 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 7, 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 7, 2010 12:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 12:40 AM
Points: 2,066, Visits: 370
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 7, 2010 2:14 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:52 AM
Points: 1,782, Visits: 6,485
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 7, 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: Friday, September 12, 2014 4:57 PM
Points: 582, Visits: 453
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 7, 2010 2:56 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 8:18 PM
Points: 17,824, Visits: 15,756
thanks for the QOD



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #948857
Posted Wednesday, July 7, 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 @ 8:38 PM
Points: 35,371, Visits: 31,912
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."

(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 7, 2010 9:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 8:11 PM
Points: 21, Visits: 95
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