ISNUMERIC(varchar)
-
Kanu, thanks for the question. While basic, it was interesting. Let's see some more.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn -
Thanks, interesting question...
-
if "101" had been an option I'd have chosen it (and been wrong)
I would not have expected isnumeric('$') to be 0
-
easy question..
there is an excellent explanation from Jeff moden..
-
pchirags (10/15/2013)
easy question..there is an excellent explanation from Jeff moden..
+1 I was thinking about the same article pchirags. 😀 I've ran into a few issues like this with IsNumeric and have done my homework on it so this was an easy one. Great question.
Everything is awesome! -
pchirags (10/15/2013)
easy question..there is an excellent explanation from Jeff moden..
Yes. That SQL Spackle is excellent as is the ensuing dicussion. Thanks for bringing it to our attention.
-
Dhruvesh Shah (10/14/2013)
Good question. Any one know why it returns 1 for some/most of special characters?ISNUMERIC returns 1 if the input can be converted to a numeric data type. It doesn't bother to tell you which data type.
In the case of '-', most data types will do (It is interpreted as -0). Similar for '.', which is interpreted as 0.0.
The '$' symbol converts to only a specific numeric data type: money (and smallmoney). It is interpreted as $0.00.
SELECT CAST('$' AS money);
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Nice question, thanks! 🙂
-
Hugo Kornelis (10/15/2013)
ISNUMERIC returns 1 if the input can be converted to a numeric data type. It doesn't bother to tell you which data type.I've yet to discover what numeric type a non-breaking space (ASCII 160) can be converted to, though honestly I haven't put much work into it as the answer would be useless to me anyway. An ISNUMERIC(value,type) function would be far more useful.
ron
-----
a haiku...NULL is not zero
NULL is not an empty string
NULL is the unknown -
ronmoses (10/15/2013)
Hugo Kornelis (10/15/2013)
ISNUMERIC returns 1 if the input can be converted to a numeric data type. It doesn't bother to tell you which data type.I've yet to discover what numeric type a non-breaking space (ASCII 160) can be converted to, though honestly I haven't put much work into it as the answer would be useless to me anyway. An ISNUMERIC(value,type) function would be far more useful.
ron
As others have said, TRY_CONVERT in SQL Server 2012 fills that gap.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hugo Kornelis (10/15/2013)
As others have said, TRY_CONVERT in SQL Server 2012 fills that gap.Ah yes, I must have glossed over it due to the "2012" part. I try not to get excited about toys I can't have. 😀
ron
-----
a haiku...NULL is not zero
NULL is not an empty string
NULL is the unknown -
Really simple one, back to basics... thanks, Kanu!
-
Nice and easy Q, thanks
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
ronmoses (10/15/2013)
I've yet to discover what numeric type a non-breaking space can be converted toMost of them I'd have thought, on the basis that in some locales a space is used as the thousands separator.
-
Had forgotten about this so it was a great reminder. Thanks
Not all gray hairs are Dinosaurs!
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply