ISNUMERIC and CAST

[DCM Fan]

SELECT ISNUMERIC('. ')

1

SELECT CAST('. ' AS [insert any numeric type])

Syntax error converting the varchar value '. ' to a column of data type
[insert any numeric type].

Any thoughts as to why SQL Server 7.0 considers '. ' to be numeric, yet can't
convert it to any numeric data type? The BOL even has this to say: "ISNUMERIC
returns 1 when the input expression evaluates to a valid integer, floating
point number, money or decimal type; otherwise it returns 0. A return value of
1 guarantees that expression can be converted to one of these numeric types."

An application that has worked for years, failed when a field of type CHAR(3)
(that should be an area code) contained a period only. I have error-checking
that tests ISNUMERIC on that field, and if it fails, alert accordingly. If it
passes, then convert it to a number, and compare it to acceptable ranges for
Area Code. (200 < AC < 999)

oops...

[Erland Sommarskog]

DCM Fan (dc****@aol.comSPNOAM) writes:

SELECT ISNUMERIC('. ')

1

SELECT CAST('. ' AS [insert any numeric type])

Syntax error converting the varchar value '. ' to a column of data type
[insert any numeric type].

Any thoughts as to why SQL Server 7.0 considers '. ' to be numeric, yet
can't convert it to any numeric data type?

You are not trying hard enough. SELECT CAST('. ' as money) returns
a value.

isnumeric() is a completely useless function. I would strongly
recommend against use of it.


--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[DCM Fan]

<<You are not trying hard enough.>>

Apparently not. I got bored after trying int, real, numeric, float and decimal.
That's why I come to this board, because I know someone else will (or has) put
in the time. Thank you.

<<isnumeric() is a completely useless function. I would strongly
recommend against use of it.>>

Nice.

I'm using PATINDEX now.

[Dan Guzman]

If you want to check for only integer digits, try LIKE:

SELECT *
FROM MyTable
WHERE NOT MyData LIKE ''%[^0-9]%' AND MyData <> ''

--
Hope this helps.

Dan Guzman
SQL Server MVP

"DCM Fan" <dc****@aol.comSPNOAM> wrote in message
news:20***************************@mb-m17.aol.com...

<<You are not trying hard enough.>>

Apparently not. I got bored after trying int, real, numeric, float and decimal. That's why I come to this board, because I know someone else will (or has) put in the time. Thank you.

<<isnumeric() is a completely useless function. I would strongly
recommend against use of it.>>

Nice.

I'm using PATINDEX now.