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.