|
|
|
SSC Eights!
      
Group: General Forum Members
Last Login: Yesterday @ 4:39 AM
Points: 936,
Visits: 1,185
|
|
|
|
|
|
SSCoach
         
Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 18,857,
Visits: 12,442
|
|
|
|
|
|
Say 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
|
|
|
|
|
Hall of Fame
       
Group: General Forum Members
Last Login: Thursday, June 13, 2013 8:50 PM
Points: 3,208,
Visits: 4,178
|
|
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.
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 AM
Points: 1,552,
Visits: 359
|
|
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...
|
|
|
|
|
Hall of Fame
       
Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 3,164,
Visits: 4,344
|
|
Good question
____________________________________________ Space, the final frontier? not any more... All limits henceforth are self-imposed. “libera tute vulgaris ex”
|
|
|
|
|
Mr or Mrs. 500
      
Group: General Forum Members
Last Login: Yesterday @ 12:43 AM
Points: 584,
Visits: 1,574
|
|
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.
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 2,547,
Visits: 3,648
|
|
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
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 1,419,
Visits: 267
|
|
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
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 370,
Visits: 853
|
|
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"
|
|
|
|