April 7, 2013 at 10:20 am
Thanks for the question.
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
April 7, 2013 at 10:51 pm
Again making the trick with length of the column.. Nice ..
--
Dineshbabu
Desire to learn new things..
April 7, 2013 at 11:19 pm
Good Question but still I have doubts in some cases like:
SELECT ISNUMERIC('.+') -- Returns 0
SELECT ISNUMERIC('+.') -- Returns 1
SELECT ISNUMERIC (',\') -- Returns 0
SELECT ISNUMERIC ('\,') -- Returns 1
:ermm:
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 7, 2013 at 11:25 pm
+1 🙂
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
April 8, 2013 at 12:49 am
An okay question, I guess. The combination of title, question, and answer options may make it very easy to overlook the actual issue, but if we cannot be expected to read code carefully, who can?
But really a shame that the explanation totally fails ijn explaining the correct answer. At first sight, all zeroes appears to be the correct answer, because the string fragment "NAME" makes all of the string constants in the INSERT statements non-numeric. Too bad the author of the question failed to include the explanation that this string fragment is just a distracter - the string column is declared to be varchar(2), so in all those cases the offending characters are stripped off.
Another relevant part of the explanation is missing as well, as is demonstrated by kapil_kk:
kapil_kk (4/7/2013)
Good Question but still I have doubts in some cases like:SELECT ISNUMERIC('.+') -- Returns 0
SELECT ISNUMERIC('+.') -- Returns 1
SELECT ISNUMERIC (',\') -- Returns 0
SELECT ISNUMERIC ('\,') -- Returns 1
:ermm:
I can help him to some extent, but not fully. I can explain the first two, and IO'm going to do that in reversed order.
SELECT ISNUMERIC('+.') -- Returns 1
That is because just . is a valid numeric (represents 0.0). A trailing zero after a decimal point can be left out - nobody is surprised by 3. being equal to 3.0. A leading zero is also optional, we all write 3.1 instead of 003.1, and we all have seen numbers starting with a dot, like .31 instead of 0.31. What is unusual is both zeroes being removed at the same time - but if the number is 0.0 and the leading and trailing zeroes are removed, all that's left is a dot.
And a plus sign before a number is also valid. We hardly ever use it - we are used to starting the number with a minus sign for negative, and omitting the plus sign for positive numbers. But it is valid to include it.
Combine 0.0 represented as . with a leading plus sign, and you get +.
SELECT ISNUMERIC('.+') -- Returns 0
After the above explanation, this one is a lot shorter. A plus sign is only valid at the first non-blank position. Just as a negative. We al,low -13.14, but we frown upon 3-1.14, 31-.14, 31.1-4, and 31.14- (though the latter is sometimes usesd by bankers and accountants).
I hope someone else can explain why a backslash at the start of the string is considered numeric, as I was surprised at that myself.
April 8, 2013 at 1:16 am
Hugo Kornelis (4/8/2013)
An okay question, I guess. The combination of title, question, and answer options may make it very easy to overlook the actual issue, but if we cannot be expected to read code carefully, who can?But really a shame that the explanation totally fails ijn explaining the correct answer. At first sight, all zeroes appears to be the correct answer, because the string fragment "NAME" makes all of the string constants in the INSERT statements non-numeric. Too bad the author of the question failed to include the explanation that this string fragment is just a distracter - the string column is declared to be varchar(2), so in all those cases the offending characters are stripped off.
Another relevant part of the explanation is missing as well, as is demonstrated by kapil_kk:
kapil_kk (4/7/2013)
Good Question but still I have doubts in some cases like:SELECT ISNUMERIC('.+') -- Returns 0
SELECT ISNUMERIC('+.') -- Returns 1
SELECT ISNUMERIC (',\') -- Returns 0
SELECT ISNUMERIC ('\,') -- Returns 1
:ermm:
I can help him to some extent, but not fully. I can explain the first two, and IO'm going to do that in reversed order.
SELECT ISNUMERIC('+.') -- Returns 1
That is because just . is a valid numeric (represents 0.0). A trailing zero after a decimal point can be left out - nobody is surprised by 3. being equal to 3.0. A leading zero is also optional, we all write 3.1 instead of 003.1, and we all have seen numbers starting with a dot, like .31 instead of 0.31. What is unusual is both zeroes being removed at the same time - but if the number is 0.0 and the leading and trailing zeroes are removed, all that's left is a dot.
And a plus sign before a number is also valid. We hardly ever use it - we are used to starting the number with a minus sign for negative, and omitting the plus sign for positive numbers. But it is valid to include it.
Combine 0.0 represented as . with a leading plus sign, and you get +.
SELECT ISNUMERIC('.+') -- Returns 0
After the above explanation, this one is a lot shorter. A plus sign is only valid at the first non-blank position. Just as a negative. We al,low -13.14, but we frown upon 3-1.14, 31-.14, 31.1-4, and 31.14- (though the latter is sometimes usesd by bankers and accountants).
I hope someone else can explain why a backslash at the start of the string is considered numeric, as I was surprised at that myself.
Thanks Hugo for the explanation 🙂
For the first part I understand the behavior but am still confused with the SELECT ISNUMERIC('.+') -- Returns 0 behavior 🙁
I am again surprised with these two statements behavior:
SELECT ISNUMERIC('.,') -- Return 1
SELECT ISNUMERIC(',.') -- Return 1
I hope someone can explain to us about these two statements behavior.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 8, 2013 at 1:28 am
kapil_kk (4/8/2013)
For the first part I understand the behavior but am still confused with the SELECT ISNUMERIC('.+') -- Returns 0 behavior 🙁
The . actually represents 0.0, with leading and trailing zero omitted.
So +. is equivalent to +0.0, which is a valid number. But .+ is equivalent to 0.0+, which is not. The plus sign (just as the minus sign) is only valid at the start of the numeric string.
I am again surprised with these two statements behavior:
SELECT ISNUMERIC('.,') -- Return 1
SELECT ISNUMERIC(',.') -- Return 1
The comma is used as the thousands seperator in America (and, as far as I know, most English speaking countries). When converting from string to money and smallmoney, thousands seperators in the string are accepted. This is implemented in a rather loose way - there is no checkk if the thousands seperators are where they should be, they are simply ignored.
April 8, 2013 at 2:17 am
Hugo Kornelis (4/8/2013)
kapil_kk (4/8/2013)
For the first part I understand the behavior but am still confused with the SELECT ISNUMERIC('.+') -- Returns 0 behavior 🙁The . actually represents 0.0, with leading and trailing zero omitted.
So +. is equivalent to +0.0, which is a valid number. But .+ is equivalent to 0.0+, which is not. The plus sign (just as the minus sign) is only valid at the start of the numeric string.
I am again surprised with these two statements behavior:
SELECT ISNUMERIC('.,') -- Return 1
SELECT ISNUMERIC(',.') -- Return 1
The comma is used as the thousands seperator in America (and, as far as I know, most English speaking countries). When converting from string to money and smallmoney, thousands seperators in the string are accepted. This is implemented in a rather loose way - there is no checkk if the thousands seperators are where they should be, they are simply ignored.
Thanks Hugo, now its clear to me :-):-)
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 8, 2013 at 2:38 am
I hope someone else can explain why a backslash at the start of the string is considered numeric, as I was surprised at that myself.
I've managed to find this info:
http://msdn.microsoft.com/en-us/library/bb510680(v=sql.100).aspx
The backslash character (\) is accepted in all string to money conversions in all languages. ISNUMERIC would return true when \ is used as a currency symbol.
http://blogs.msdn.com/b/michkap/archive/2005/10/12/479561.aspx
_______________________________________________
www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)
April 8, 2013 at 3:27 am
nenad-zivkovic (4/8/2013)
I hope someone else can explain why a backslash at the start of the string is considered numeric, as I was surprised at that myself.
I've managed to find this info:
http://msdn.microsoft.com/en-us/library/bb510680(v=sql.100).aspx
The backslash character (\) is accepted in all string to money conversions in all languages. ISNUMERIC would return true when \ is used as a currency symbol.
Ah thanks, that helps. I followed the link and read the surrounding text. Apparently, the backslash can be used as a currency symbol in Japanese and Korean languages. The link you supplied describes a behaviour change, where until SQL Server 2000 that backslash was only accepted if you actually had such a language as an active language, but since SQL Server 2005 the backslash is considered a valid currency symbol regardless of language setting.
And of course, it makes sense that if you consider the backslash to be a valid currency symbol, it is valid at the start of a string when converting to money or smallmoney.
EDIT: Corrected mistake with quote / end-quote tags
April 8, 2013 at 3:38 am
Hugo Kornelis (4/8/2013)
....I hope someone else can explain why a backslash at the start of the string is considered numeric, as I was surprised at that myself.
possibly can be this...
http://www.tru64unix.compaq.com/docs/base_doc/DOCUMENTATION/V40F_HTML/AQTLTBTE/DOCU_010.HTM
(scroll to the section - 1.8.3.4 Numeric Escape Sequences)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
April 8, 2013 at 5:10 am
Thanks for the most obvious answer to turn out to be something not known. 🙂
Thanks to Hugo for his explanation.
Angad
--Angad Singh
If I Stop Learning, I Cease to Be A SIKH !
April 8, 2013 at 6:03 am
Hugo Kornelis (4/8/2013)
nenad-zivkovic (4/8/2013)
I hope someone else can explain why a backslash at the start of the string is considered numeric, as I was surprised at that myself.
I've managed to find this info:
http://msdn.microsoft.com/en-us/library/bb510680(v=sql.100).aspx
The backslash character (\) is accepted in all string to money conversions in all languages. ISNUMERIC would return true when \ is used as a currency symbol.
Ah thanks, that helps. I followed the link and read the surrounding text. Apparently, the backslash can be used as a currency symbol in Japanese and Korean languages.
I don't think backslash really is a valid symbol for Yen or Won. It got used for Yen (and Yuan Renminbi, as that's the same symbol) because someone picked an already occupied 7-bit code point, and presumably was used for Won someone also picked that same codepoint for Won. It certainly wasn't used because any Korean or Japanese (or Chinese) person though that "\" was an appropriate symbol for their currency.
Anyway, IBM and its asian customers seemed to regard it as a problem: see for example this or this. I recall also a recomendation to switch to a different code page in some circumstances on some systems to allow currency to be displayed correctly (with the Yen/Yuan Renminbi symbol, not backslash) and I imagine there was the same sort of recommendation for Won. So perhaps the idea that \ is a valid currency symbol doesn't have much support, except in the imagination of some people who have got accustomed do using the wrong currency symbol.
Anyway, with the introduction of code page Windows 1252 there was no longer any excuse for using \ for Yen, because that had a separate code point A5 instead of backclash's 5C (Windows 1252 has twice as many code points as the original ascii). It's a pity there was no Windows 1252 code point for Won.
edit: Just to add to the story of isnumeric's lunacy: isnumeric(nchar(8361)) delivers 0: the Won symbol is not numeric. But isnumeric(nchar(165)) delieves 1; the Yen symbol is numeric. Go figure :ermm:.
Tom
April 8, 2013 at 7:18 am
Hugo Kornelis (4/8/2013)
An okay question, I guess. The combination of title, question, and answer options may make it very easy to overlook the actual issue, but if we cannot be expected to read code carefully, who can?But really a shame that the explanation totally fails ijn explaining the correct answer. At first sight, all zeroes appears to be the correct answer, because the string fragment "NAME" makes all of the string constants in the INSERT statements non-numeric. Too bad the author of the question failed to include the explanation that this string fragment is just a distracter - the string column is declared to be varchar(2), so in all those cases the offending characters are stripped off.
I totally agree... my initial thought was all 0's.. because of NAME, but I then realized it was too simple, and went back.
However, this again stops being as much about ISNUMERIC and more about truncation... *sigh*
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply