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 2000 Forums
 SQL Server Development (2000)
 ISNUMERIC function -- not what I expected... SQL2000 SP1 ??

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts
Posted - 2001-12-29 : 12:12:11
Robert Wafle writes "When I execute select isnumeric (',') or isnumeric ('.')

my server returns a value of 1

the docs say that if isnumeric retuns 1, then the expression can be converted to an integer, float, money or decimal... I find this very annoying.. I need an isinteger() function.. since isnumeric is bogus. (not useful for answering a straight question.. like will this data type convert to an integer??)

DOCS READ:

Remarks
ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types.

EXPERIENCE GIVES ME:

select isnumeric (',') as isNumeric

isNumeric
-----------
1

select isnumeric ('.') as isNumeric

isNumeric
-----------
1


select cast (',' as int)

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value ',' to a column of data type int.

select cast ('.' as int)

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '.' to a column of data type int.

select cast (',' as money)

---------------------
.0000

(1 row(s) affected)

select cast ('.' as money)

---------------------
.0000

(1 row(s) affected)


Arraaaagh. Well.. I've come to realize that the function works as described.. but not how I would like.. guess I'll have to write my own functions and waste some Time, Disk, CPU and Memory.. grrr.. isInteger() isMoney() isDouble() isFloat() etc.. guess I could call the function and catch the @@Error.. "

nr
SQLTeam MVY

12543 Posts
Posted - 2001-12-29 : 15:16:40
As you point out isnumeric isn't very helpful although it dos what it says. Usually functions with names like this should be treated with caution as they are just what the implementer thinks of as numeric not what the application expects - look at isdate in VB.
If you are suggesting trying a convert and catching @@error then I would advise against it unless this is a genuine error - i.e. not just part of data validation.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts
Posted - 2001-12-30 : 03:09:55
OK Robert, I give up. Why would you ever have JUST a comma, or JUST a period? I think this may point to a different flaw.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...
Go to Top of Page

smccreadie
Aged Yak Warrior

505 Posts
Posted - 2001-12-30 : 06:27:03
You should really try to clean up the data before it get's to SQL Server. Otherwise your users get somewhat cryptic database errors.

You don't say what your development is but there are a number of javascript and vbscript ways to ensure that a value is an integer. Apply these before sending the data to SQL.
Go to Top of Page

aiken
Aged Yak Warrior

525 Posts
Posted - 2001-12-30 : 16:47:35
However, if you really need it on the SQL side, a little function that does something like:


if notIsNumeric(@vcWhat)
  return 0

if patindex(@vcWhat,'%,%')>0
  return 0

if patindex(@vcWhat,'%.%')>0
  return 0

return 1


...shouldn't be all that hard on the CPU.

Cheers
-b
Go to Top of Page

Nazim
A custom title

1408 Posts
Posted - 2001-12-31 : 02:15:06
Aiken, the problem is with isnumeric function. it doesnt works as expected. so, the code you have written , will not help him much. As suggested by smccreadie its always good cleaning up your data using vb,vbscript, javascript. there you will find pretty straight forward functions which will work as expected.

otherwise you have to write a stored procedure in Sql Server . treating the value as string and checking each character in it to . Dont rely on isnumeric function , check this post too
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11730 it finds another kinda bug in it.

HTH
Go to Top of Page

Onamuji
Aged Yak Warrior

504 Posts
Posted - 2001-12-31 : 14:24:29
I don't think ISNUMERIC is wrong it just looks to be like the equivalent of VAL() function in VB. If the first character is numeric then it assumes the entire string is numeric.

Here is another issue with ISNUMERIC. Say you do SELECT ISNUMERIC(INT, '$11,112.24') ... WON'T WORK ... right... WELL IF YOU DO

SELECT CONVERT(INT, CONVERT(MONEY, '$11,112.23')) That works fine :-) So as long as you are sure there are no letters in the number converting to money usually works well for stripping out the normal formating characters.

Just something I ran across a while ago ... hope it helps a little...

- Onamuji
Go to Top of Page

RobWafle
Starting Member

38 Posts
Posted - 2001-12-31 : 16:54:05
Mr NR,
I was just thinking of a lazy way to write that function. I guess I was trying to say exactly what you said.

"Usually functions with names like this should be treated with caution as they are just what the implementer thinks of as numeric not what the application expects.."

In saying that I would catch the error, I was eluding that in theory, I could write two functions:

"isInteger" and tried to cast to an int inside of it (if fails, then not int)
"isMoney" and tried to cast to a money inside of it (if fails, then not money)

because I guess I really wanted "isInteger" because I have no need for whole numbers in my app.

How would I know that MS thinks that a ',' and '.' are numeric!?!?
I sure don't think so.. but.. there may be other items I haven't thought of.. maybe the '&!*!@' is numeric too! :) (j/k)

Does anyone know how to use the LIKE clause and say LIKE [0-9][0-9][0-9]..... I can do it in VB, but I have lots of data I need to clean up and test..


AjarnMark, well.. I realize I was trying to use the isnumeric function for something other than it was designed for (isInteger)

smccreadie, yup. I could do that, but this particular problem has nothing to do with the users entering bad data. :)

aiken, patIndex?? .. seems like you're kidding about it being hard on the CPU there bud! :) If you would be so kind, I'm going to post some code I wrote for you to look at. It seems to take about 2 minutes to look through a small amount (350,000 rows) of existing crap data and identify all the bad rows.. search for CUSIP in SQLTEAM.COM .. I made a new post.

Nazim, Thanks! I'll read that.

Onamuji, as I should have specified.. I realized that I am trying to use isnumeric as "isInteger"..


OK, now.. here's something that should probably go in another post, but since I got all this great feedback from you guys, I want to share it.. If you are interested please search SQLTEAM.COM for a keyword "CUSIP".. You'll see how I have used the isnumeric function.
Go to Top of Page

aiken
Aged Yak Warrior

525 Posts
Posted - 2001-12-31 : 20:53:46
Nazim, I think you missed the point of my proposed function. My idea was basically to ride on the coattailes of IsNumeric; if IsNumeric says something is *not* numeric, then it most certainly isn't. However, if IsNumeric says something *is* numeric, it's trival to check for a couple of characters that IsNumeric gets wrong (when thinking about integers, anyways).

Manually looping through each character will have more overhead than using PATINDEX; one way you've got an interpreted loop, the other a built-in function that does much the same thing.

As for PATINDEX performance, I may have misunderstood the original question; I thought it was a data validation issue, not a "frequently searching through 350,000 records for not numeric values" question. Even still, I'd stand by patindex as a reasonable solution, at least compared to the alternatives.

I have an add-internet-email-to-db routine that does more than 30 patindexes per go (including many tougher beginning wildcard ones, like "%@spammer.com"), and it handles more than 10 emails/second with no noticeable performance hit on a fairly slow (2x650mhz) SQL server. To confirm, I just turned my filter off for a few minutes and then back on, and perfmon showed no significant change in CPU load (it averages about 25% on both CPU's, so if it was 1%, I could have missed it). Unless you're talking about a much higher load, I'd be more concerned with code maintainability than raw performance.

If you are after the fastest possible performance, you might consider using an extended stored procedure and writing a DLL to do the job; that will certainly be blazingly fast, if a bit scary to implement.

No matter what, I would reccomend a user defined function rather than a stored procedure, as functions are optimized for returning results.

Cheers
-b



Edited by - aiken on 12/31/2001 21:01:17
Go to Top of Page

RobWafle
Starting Member

38 Posts
Posted - 2001-12-31 : 23:16:59
Aiken,

Your advice seems very firm. I took the same approach, trying to look out for the cases where isnumeric fails. I used the LIKE clause because I could not think of another way to do this. I will try the PATINDEX as well. Leveraging a built in function is always a good idea. :) I also did write a function, as you may see in the article I mentioned.

I am going to identify my bad data once, then clean it out of the table, however I am going to be parsing in a lot more data, and I would like to use this function in two ways. Since the data I am parsing in is not well formed, I think I can use it to test EACH row to see if the data is there. However, I may get this data from more than one source, and I don't want someone else to screw it up.. so I am tempted to write a trigger to try to make inserts fail.. I don't yet have good security, so I can't force the inserts through a stored procedure as I would like.. We'll see... I'm going to check the other post now.. see if you wrote anything there.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site