ISNUMERIC

Question

Post reply

ISNUMERIC

Viewing 15 posts - 16 through 30 (of 42 total)

You must be logged in to reply to this topic. Login to reply

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 1

Thanks for the question

Always define the length of your VARCHARS people 😀

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 2

L' Eomot Inversé (3/6/2013)
Nice question, but I don't much like the heading. Fortunately I've learnt not to pay much attention to QotD headings.

Yeah, I understand everyone's complaint about the header, but if it was accurate then that would sort of give away the answer.

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 3

...
Because that would point you directly to the answer. I would have called it "You figure it out."
...

I like that. All questions going forward should have the title "You figure it out"

Dana Medley SSCertifiable Points: 6764 More actions · Answer 4

Ooooooh, you almost got me, but I caught it at the last second before pulling the trigger. :-D. Thanks for the question.

Everything is awesome!

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

lots of people have issues with Header of the QOD 😎

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

mtassin SSC-Insane Points: 23099 More actions · Answer 6

Lokesh Vij (3/5/2013)
+1 🙂
Well this question is about ISNUMERIC and VARCHAR both. I realized the VARCHAR gotcha and marked the correct answer.

Same... but I can also see the point... the subject isn't really about what ISNUMERIC will test or won't... it's about what happens when you don't give varchar a length..

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

jmoney 69422 SSC Enthusiast Points: 151 More actions · Answer 7

Very good question!

Though I make a habit of specifying the n in a varchar declaration, I had no idea that in a DECLARE, without a specified n, it would default to 1. I also picked up the tidbit from another poster that a cast/convert without n would default to 30. For me, since I work in SQL Server all day, this was very valuable.

Thanks again!

chgn01 SSCarpal Tunnel Points: 4420 More actions · Answer 8

Please try this:

SET @vchrIsNumeric='$NAME',

Is Numeric or Is not Numeric?

--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius

Ken SSCoach Points: 17191 More actions · Answer 9

chgn01 (3/6/2013)
Please try this:
SET @vchrIsNumeric='$NAME',
Is Numeric or Is not Numeric?

That will return Is Numeric as well. Same thing if you do '£NAME'. I think any currency character will evaluate as numeric. I tried it with the symbols for the US cent, Yen, US dollar and British pound.

chgn01 SSCarpal Tunnel Points: 4420 More actions · Answer 10

Thank you Fire Drill!

--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius

Ron McCullough SSC Guru Points: 63877 More actions · Answer 11

Agree with the title of the QOD being missleading, and to make sure people do NOT conclude, incorrectly, how ISNUMERIC works may I suggest that every one read this excellent SQL SPACKLE article by Jeff Moden

http://www.sqlservercentral.com/articles/IsNumeric/71512/

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Revenant SSC-Forever Points: 42467 More actions · Answer 12

Slightly misleading, yes... But thanks anyway, Ramana!

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 13

Hugo Kornelis (3/6/2013) I also think that this adds a nice touch of realism. If a junior on my team made this mistake and came to me after not being able to figure it out, they would probably also tell me that they have a problem with ISNUMERIC.

I actually like the title, for the same reason given by Hugo. A person could beat his/her head against the wall, not noticing that the declare statement did not specify a length. This is what debugging is all about.

Ken SSCoach Points: 17191 More actions · Answer 14

bitbucket-25253 (3/6/2013)
Agree with the title of the QOD being missleading, and to make sure people do NOT conclude, incorrectly, how ISNUMERIC works may I suggest that every one read this excellent SQL SPACKLE article by Jeff Moden
http://www.sqlservercentral.com/articles/IsNumeric/71512/

Thanks for the link. I was wondering what other characters might fall into the IsNumeric trap. I prefer the regex approach myself.

Ken

TomThomson SSC Guru Points: 104768 More actions · Answer 15

KWymore (3/6/2013)
chgn01 (3/6/2013)
Please try this:
SET @vchrIsNumeric='$NAME',
Is Numeric or Is not Numeric?
That will return Is Numeric as well. Same thing if you do '£NAME'. I think any currency character will evaluate as numeric. I tried it with the symbols for the US cent, Yen, US dollar and British pound.

No, it's not rational like that.

Maybe it depends on what your language is, but if it's us_english most (25 out of 34) of the currency characters listed on the BOL Using Monetary Data page well deliver 0, not 1, when fed to IsNumeric, since the only 9 of the symbols listed there that deliver 1 are $(dollar), ¢(cent), £(pound), ¤(currency), ¥(yen), ¢(colón), £(lira), €(euro), and (Full Width dollar).

There's something wonderfully irrational about recognising the Italian lira synbol as numeric, but not the Greek drachma, the German Pfennig, the Spanish peseta or the French franc. Why return 1 for the FW Dollar but not for the FW versions of cent, pound, or Yen? Why 1 for colón but 0 for peso? The yen symbol is numeric, but the won symbol isn't, and the Chinese remnibi(?, nchar(20803), hex 5143) is not only not numeric but not even listed on BOL Using Monetray Data page linked above.

It's not just irrational. It's totally unusable.

If you want to build a multi-national system, where a single server provides interfaces in several languages and quotes prices in several currencies don't allow any developer ever to write code that calls IsNumeric (if anyone thinks that the front end should do translation instead of having the required text in the database, they clearly don't know what a mangled mess even the best automatic translation makes of things).

Tom