SEO

#1 (**permalink**) 02-29-2008, 02:39 AM

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...

#2 (**permalink**) 02-29-2008, 02:39 AM

DCM Fan (dcmfan@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, sommar@algonet.se

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

#3 (**permalink**) 02-29-2008, 02:39 AM

<<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.

#4 (**permalink**) 02-29-2008, 02:39 AM

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" <dcmfan@aol.comSPNOAM> wrote in message
news:20040428223934.15149.00000412@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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

ISNUMERIC and CAST