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 123»»»

Numeric or Not Numeric Expand / Collapse
Author
Message
Posted Saturday, February 20, 2010 1:23 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:43 PM
Points: 945, Visits: 1,234
Comments posted to this topic are about the item Numeric or Not Numeric

Amol Naik
Post #869710
Posted Sunday, February 21, 2010 10:21 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 4:53 PM
Points: 21,617, Visits: 15,271
nice question



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 #869870
Posted Sunday, February 21, 2010 4:15 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, December 26, 2011 4:13 PM
Points: 689, Visits: 410
Hi,

Interesting question.

But this code - based on the question - fails with Error converting data type varchar to float.

if( select isnumeric( @PrincipalAmount )) = 1
select convert( float, @PrincipalAmount )
else
print 'Sorry'
;


That is, isnumeric doesn't guarantee that the conversion will work, just that its likely to. It would be better imho if isnumeric was more robust. What is the point of using it, if will have to use exception handling anyway to catch errors that isnumeric missed?

Cheers
David
Post #869971
Posted Sunday, February 21, 2010 5: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, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
ISNUMERIC() function always ignores commas while validating data

The question is good, but the explanation is wrong. In some cases commas are not ignored:
SELECT ISNUMERIC('-1') -- the result is 1
SELECT ISNUMERIC(',-1') -- the result is 0, this shows that the comma is not ignored

Another example:
SELECT ISNUMERIC('') -- the result is 0
SELECT ISNUMERIC(',') -- the result is 1; if ISNUMERIC() ignored the comma,
-- the result would be 0

David Todd-242471 (2/21/2010)

But this code - based on the question - fails with Error converting data type varchar to float.

if( select isnumeric( @PrincipalAmount )) = 1 
select convert( float, @PrincipalAmount )
else
print 'Sorry'
;

That is, isnumeric doesn't guarantee that the conversion will work, just that its likely to.

Float is not the only numeric data type The conversion to money works fine.
Post #869979
Posted Sunday, February 21, 2010 9:49 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 1,999, Visits: 369

That is, isnumeric doesn't guarantee that the conversion will work, just that its likely to
Float is not the only numeric data type The conversion to money works fine.


conversion to money alone works... nothing else viz. float, int, numeric works...
Post #870047
Posted Sunday, February 21, 2010 11:19 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: Today @ 2:29 AM
Points: 3,902, Visits: 5,069
Good question

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #870084
Posted Monday, February 22, 2010 12:32 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, July 3, 2014 9:19 PM
Points: 605, Visits: 1,691
ISNUMERIC is more general than CONVERT. The following is also valid (in 2008):

SET @PrincipalAmount = '100e2'

It will convert to float, but not money. With the QotD I was more concerned about the decimal place with no trailing zeroes.

S.
Post #870104
Posted Monday, February 22, 2010 1:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,834, Visits: 3,866
We already had a similar question before.
If a number string can be converted to any numeric type, ISNUMERIC returns 1.

Among the known "issues" with this are:
money: 1,,,,,,1 is a valid money value (obviously it does not make sense though)
float: 1e1 is a valid float value
float: 1d1 is a valid float value

IsNumeric is consistent in the way that it verifies that it can convert to any SQL Server numeric data type (which includes monetary datatypes & float).
So blame it on the conversion functions instead:)


Best Regards,
Chris Büttner
Post #870119
Posted Monday, February 22, 2010 2:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 15, 2014 4:17 PM
Points: 1,721, Visits: 468
Good question. And it also raises a further point in my mind ...
Is there a need to standardise numerical representation across every locale ?

I ask this because, in some locales, the number expressed as
700,000
could represent
700 [decimal point] 000
that is, 700 expressed to three decimal places.
In other locales, it would be
700 [thousands separator] 000

I have noticed a mixture of decimal point indicators on invoices these days, and it certainly isn't unusual to get one using the comma [,] as a decimal point rather than the more logical full-stop [.].

Kenneth Spencer

Post #870176
Posted Monday, February 22, 2010 3:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 8, 2014 2:14 AM
Points: 387, Visits: 923
Good Question:

Also
DECLARE @PrincipalAmount VARCHAR(15)
SET @PrincipalAmount = '£700,000'
SELECT ISNUMERIC(@PrincipalAmount)

returns 1 as does

DECLARE @PrincipalAmount VARCHAR(15)
SET @PrincipalAmount = '$700,000'
SELECT ISNUMERIC(@PrincipalAmount)

See http://msdn.microsoft.com/en-us/library/ms188688.aspx for other recognised currency symbols



RedLlewy
"The Plural of Anecdote is not Data"

Post #870191
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse