SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Numeric or Not Numeric


Numeric or Not Numeric

Author
Message
AmolNaik
AmolNaik
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1243 Visits: 1234
Comments posted to this topic are about the item Numeric or Not Numeric

Amol Naik
SQLRNNR
SQLRNNR
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40369 Visits: 18565
nice question



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

David Todd-242471
David Todd-242471
Say Hey Kid
Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)

Group: General Forum Members
Points: 691 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
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3890 Visits: 4408
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.
ziangij
ziangij
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3269 Visits: 377

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...
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10982 Visits: 7326
Good question

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Fal
Fal
SSChasing Mays
SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)

Group: General Forum Members
Points: 602 Visits: 1803
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.
Christian Buettner-167247
Christian Buettner-167247
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4085 Visits: 3889
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 insteadSmile

Best Regards,

Chris Büttner
kaspencer
kaspencer
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2538 Visits: 844
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

You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer
RedLlewy
RedLlewy
SSC-Addicted
SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)

Group: General Forum Members
Points: 486 Visits: 1165
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"


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search