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 «««1112131415»»»

Why doesn’t ISNUMERIC work correctly? (SQL Spackle) Expand / Collapse
Author
Message
Posted Friday, September 14, 2012 12:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
jeffem (9/14/2012)
Very nice article! There is a typo:

"164 (Yen sign)" should actually reference 165.


Crud. My apologies. I noticed that in the past (this article has been posted a couple of times now) and just haven't gotten to fixing that (there's another error IIRC but can't recall what that is so I'll have to look for it). Thank you for both the catch and the reminder.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1359564
Posted Friday, September 14, 2012 1:07 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:54 AM
Points: 304, Visits: 518
Jeff Moden (9/14/2012)
Lisa Slater Nicholls (9/14/2012)
I see that the IsReallyNumeric codeset on http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html, pointed to by various people here, does pretty much what I want.


Oh, be careful, Lisa. The "filter" built into the method of the article can discriminnate for or against just about anything you want, as well. The problem with the method used in the link you cited is that it uses a scalar function which has some pretty severe performance problems that I wouldn't even justify the use of for supposedly small data sets.

The method in the article can use an "iSF" or "Inline Scalar Function" (for lack of a better term). Please see the following information for more on that subject.
http://www.sqlservercentral.com/articles/T-SQL/91724/


Right Jeff, thanks -- I understand and appreciate the clarification. But, FWIW, I was saying "this is the functionality that I, and others, want built into SQL Server" -- IsInteger() etc. I'm not likely to use the function as-is, except in the following case, which happens to be an important use case in my world: a set of string values is brought into a stored procedure from a fairly primitive web interface for the purpose of an upsert statement, always handling ONE row. Other, similarly costly, validations would also be performed in the procedure, and it's really appropriate here.

Finally, though, since I have your attention <gd&r>... I thought this piece of code was pretty cool as a method : http://codecorner.galanter.net/2009/04/03/tsql-isnumeric-function-returns-false-positives/ -- but would like your opinion on any downsides?

>L<
Post #1359591
Posted Friday, September 14, 2012 1:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 10:58 AM
Points: 6, Visits: 119
Converting into money and from there into int, float or some other numeric data type could be a valid solution for many cases and would perform better than a UDF. Just another option that can save precious time for some people looking into the forum.

Besides, I do not think it is correct to claim that ISNUMERIC() does not work as the title affirms.
Post #1359601
Posted Friday, September 14, 2012 2:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
josuecalvo (9/14/2012)
Converting into money and from there into int, float or some other numeric data type could be a valid solution for many cases and would perform better than a UDF. Just another option that can save precious time for some people looking into the forum.

Besides, I do not think it is correct to claim that ISNUMERIC() does not work as the title affirms.


If you read the article, I also don't think that ISNUMERIC() doesn't work for what it was designed to do. The title of the article was specifically designed to help people that think ISNUMERIC is broken to find a better answer when they Google.

Your code example also provides a test only for single characters. While I appreciate your suggestion above, I'd like to see the manifestation of your actual working code to solve the problem of how to determine if a string value can be converted to an integer that has the same as the integer value a human would derive from lookinng at the string. For an example, a human can look at a string like 1,2,3,4,5.6 (notice the decimal point, as well) or even just 12345.6 or 1E-3 and realize that it's not a single integer value and would reject it. Please show in working code how your suggestion would arrive at the same conclusion.

I'm not asking this simply as a challenge. It's very possible that you have a more effective notion than anyone else has presented and would like to see it in the form of working code because I'm just not seeing how it could be made to work more effectively than those things already presented. I'm always up for learning something new and that's especially why I like the discusssions that follow these articles.

So far as UDFs being some form of performance tax goes, not all UDFs are created equal. Using UDFs isn't a bad thing. Using Scalar and Muli_Line table value functions is.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1359641
Posted Friday, September 14, 2012 3:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 10:58 AM
Points: 6, Visits: 119
What you are asking for is called regex. Google it, there are plenty of very good sites about it. I do not think I can improve its flexibility, so no need to waste time on that.
Post #1359696
Posted Friday, September 14, 2012 4:24 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:48 PM
Points: 8,545, Visits: 9,034
duplicate post deleted

Tom
Post #1359717
Posted Friday, September 14, 2012 4:26 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:48 PM
Points: 8,545, Visits: 9,034
Lisa Slater Nicholls (9/14/2012)
But, FWIW, I was saying "this is the functionality that I, and others, want built into SQL Server" -- IsInteger() etc.

That's fair enough. If you want onme (or perhaps several) functions which are different from IsNumeric it's agood idea to say so. It would of course be better to say so in something clearly intended as input for the standards committees, rather than here.
Finally, though, since I have your attention <gd&r>... I thought this piece of code was pretty cool as a method : http://codecorner.galanter.net/2009/04/03/tsql-isnumeric-function-returns-false-positives/ -- but would like your opinion on any downsides?>L<

I can see two downsides: the elephant in the room is the simple fact that isnumeric doesn't return false positives, despite the crazy URL and the crazy text on the page - so the whole webpage is based on a falsehood, and when read by anyone who doesn't recognise and understand that it is an offensively damaging webpage. The second downside is related to the fact (apparently unknown by the author of the article) that "D" and "E" are not the same letter. Those two little problems seem to me to demonstrate that the page isn't worth the screen-space it takes up when anyone bothers to display it. We could then add in questions about the use of commas, points, and so on, whether the test works independently of language settings (although if the test is interopreted as relative to current language settings that might not be a problem - of course if you look at Indian numerics do you work in thousands or in hundreds for seperators after the first thousand - you had better understand both, but the code suggested on the page you reference understands neither).

But you didn't ask me, you asked Jeff; I think his paper that instigated this discussion makes it pretty clear that he thinks that IsNumeric returns no false positives, but of course I can easily be wrong.


Tom
Post #1359719
Posted Saturday, September 15, 2012 1:27 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:54 AM
Points: 304, Visits: 518
L' Eomot Inversé (9/14/2012)
Lisa Slater Nicholls (9/14/2012)
But, FWIW, I was saying "this is the functionality that I, and others, want built into SQL Server" -- IsInteger() etc.

That's fair enough. If you want onme (or perhaps several) functions which are different from IsNumeric it's agood idea to say so. It would of course be better to say so in something clearly intended as input for the standards committees, rather than here.
Finally, though, since I have your attention <gd&r>... I thought this piece of code was pretty cool as a method : http://codecorner.galanter.net/2009/04/03/tsql-isnumeric-function-returns-false-positives/ -- but would like your opinion on any downsides?>L<

I can see two downsides: the elephant in the room is the simple fact that isnumeric doesn't return false positives, despite the crazy URL and the crazy text on the page - so the whole webpage is based on a falsehood, and when read by anyone who doesn't recognise and understand that it is an offensively damaging webpage. The second downside is related to the fact (apparently unknown by the author of the article) that "D" and "E" are not the same letter. Those two little problems seem to me to demonstrate that the page isn't worth the screen-space it takes up when anyone bothers to display it. We could then add in questions about the use of commas, points, and so on, whether the test works independently of language settings (although if the test is interopreted as relative to current language settings that might not be a problem - of course if you look at Indian numerics do you work in thousands or in hundreds for seperators after the first thousand - you had better understand both, but the code suggested on the page you reference understands neither).

But you didn't ask me, you asked Jeff; I think his paper that instigated this discussion makes it pretty clear that he thinks that IsNumeric returns no false positives, but of course I can easily be wrong.


I was not concerned in my question with whether IsNumeric returns false positives -- having examined the documentation closely myself (long before this discussion) I am pretty sure that IsNumeric is correct according to its docs. And I am not trying to validate the page contents as an article, just the code itself.

I just thought the code in the article was an interesting way to do something. It looked efficient. I had never tried to do anything that way, and not knowing enough, wanted to know if it was considered generally valid.

While, yes, I asked Jeff, I do appreciate your opinion too and anybody else's -- after all, when I first cited the code I think I explicitly asked for anybody's response! Thank you for yours.

If you think that the page is offensive and damaging, by all means write to the author and perhaps s/he will improve it or, if sufficiently in agreement with your concerns, remove it. Nobody deliberately writes to mislead people on technical issues.

Regarding whether it would be a "better" idea to write to the standards committee, I do take your point. Discussions here, however, are often a way to gather information and consensus before trying to suggest a generalized, well-thought-out recommendation to a standards committee -- at least in my world. This discussion helped me see that many people had the same issues that I did and came up with a number of ways to describe what they wanted -- a thing separate from IsNumeric, and also separate from Jeff's IsDigits functionality -- and my original message was an attempt to say "there seem to be a lot of us, it is a suggestion worth pursuing, isn't it?"

>L<
Post #1359838
Posted Monday, September 17, 2012 1:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 2,826, Visits: 3,866
Lisa Slater Nicholls (9/14/2012)
ronmoses (9/14/2012)
Rather than parsing the string, how about attempting to CAST the string in a TRY...CATCH block and return a pass/fail value on that basis?

ron


The problem here would be performance, no? Especially if you were trying to do this in-line in a sql statement (for example an update), you'd need a function?

>L<

You cannot use a function, as you cannot use TRY / CATCH (side affecting) within functions.
Otherwise I would not have used a procedure in my example above.


Best Regards,
Chris Büttner
Post #1360012
Posted Monday, September 17, 2012 4:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:57 AM
Points: 7,112, Visits: 6,280
josuecalvo (9/14/2012)
What you are asking for is called regex. Google it, there are plenty of very good sites about it. I do not think I can improve its flexibility, so no need to waste time on that.


Wow. The way it is worded, this response comes off as a little rude.

Jeff is a local SQL Expert who has contributed a lot to this community and to the SQL Server community at large. All he did was ask you if *you* had a solution (different code) that might work better than his, and he did so very politely. He's hardly a rookie that needs to be schooled in the use of Google or Books Online.

In the future, you might want to consider a better way of phrasing your replies before responding like the above. You might also consider researching the person you're attempting to "school" before you treat them so poorly. You're not likely to endear yourself to many people on these forums by attacking one of our top resources. Jeff's helped a lot of people in these parts.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1360085
« Prev Topic | Next Topic »

Add to briefcase «««1112131415»»»

Permissions Expand / Collapse