IsNumeric is returning true (1) for wrong values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

IsNumeric is returning true (1) for wrong values

Author

Topic

juliano.net
Starting Member

10 Posts

Posted - 2008-11-18 : 06:51:33

If my table column has a value like 01,00.23, the IsNumeric function is returning 1 instead of 0.

Why? Is there any way to try to convert a value and don't throw an exception?

Thanks.

[]'s
Juliano
.Net Developer

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-11-18 : 06:55:32

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 07:13:41

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

juliano.net
Starting Member

10 Posts

Posted - 2008-11-18 : 07:16:26

This didn't work too.

I tried select dbo.Is_numeric('1,00.23') and it returned 0, that's ok, but when I tried select dbo.Is_numeric('1,000.23'), what is an english decimal number, it also returned 0.

[]'s
Juliano
.Net Developer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 08:56:23

quote:
Originally posted by juliano.net

This didn't work too.

I tried select dbo.Is_numeric('1,00.23') and it returned 0, that's ok, but when I tried select dbo.Is_numeric('1,000.23'), what is an english decimal number, it also returned 0.

[]'s
Juliano
.Net Developer

what it does is basically give 1 only for those which are numeric in real sense. the second one didnt return 1 because of presence of ,. What exactly is type of validation you're looking? what all are allowable values to you?

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-19 : 07:31:40

Run the following query and tell why it work like these?

SELECT ISNUMERIC('4357952,34678965')
SELECT ISNUMERIC('4357952,346789658')
SELECT ISNUMERIC('43579,34678')
SELECT ISNUMERIC('43579526,34678965')

It returns 1,0,0,0

====================================================
you realize you've made a mistake, take immediate steps to correct it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 07:36:55

Please provide some sample data and your expectation wether or not it should be treated as a numeric value.

E 12°55'05.63"
N 56°04'39.26"

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-19 : 08:07:53

Thats a Phone Number field, User enters multiple phone numbers using , separator.

====================================================
you realize you've made a mistake, take immediate steps to correct it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 08:16:19

SELECT * FROM dbo.fnParseList(',', @UserParameter)
WHERE data NOT LIKE '%[^0-9]%'

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

E 12°55'05.63"
N 56°04'39.26"

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-19 : 08:21:33

Nice Work, Thanks you so much

====================================================
you realize you've made a mistake, take immediate steps to correct it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources