March 6, 2013 at 12:33 pm
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
March 6, 2013 at 12:36 pm
A sneaky one today 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 6, 2013 at 1:41 pm
L' Eomot Inversé (3/6/2013)
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).
Thanks for the additional insight Tom. It is curious that more nationalities are not covered.
March 6, 2013 at 4:38 pm
Lokesh Vij (3/5/2013)
happycat59 (3/5/2013)
Mmm, why have "ISNUMERIC" as the heading for this question ? This question is not really about the ISNUMERIC function. How about getting the heading to match what the question is really about instead of deliberately trying to mislead ?This question is about the default length used for the VARCHAR data type. I am expecting a variation on this question to pop up soon where the answer is VARCHAR (30).
+1 🙂
Well this question is about ISNUMERIC and VARCHAR both. I realized the VARCHAR gotcha and marked the correct answer.
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 6, 2013 at 9:55 pm
But right people will concentrate on the question and not on the title.
It is nice question...
Regards,
Ravi.
March 7, 2013 at 1:34 am
Nice one....
March 7, 2013 at 2:54 am
I got it,a little tricky though.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
March 7, 2013 at 9:55 am
Thanks - Good question and made me think.
Thanks also to Tom for the other info, it is good to have.
M.
Not all gray hairs are Dinosaurs!
March 8, 2013 at 6:18 am
Wow, very trick question. Very interesting comments. Thanks.
March 22, 2013 at 10:46 am
Nice Question..
Learned something different..
Thanks..
April 3, 2013 at 6:35 am
it seems easy question ... and some wrote quite a book with so many comments for an so easy one.!!
interesting though
April 9, 2013 at 1:24 am
Aahhh. What an easy question on first view. but just a second thought and you can sniff the trap. good question anyways. 🙂
June 22, 2015 at 2:18 am
Default length 1.
So that was the fly in the ointment.
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply