But the documentation is NOT correct

The documentation says

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.

(Cut and pasted from books online)

Yet the following, one of many, example shows this is not true.

select isnumeric(char(9))
select convert(int, char(9))

----
1

(1 row(s) affected)

Server: Msg 245, Level 16, State 1, Line 2
Syntax error converting the varchar value '' to a column of data type
int.

So, besides filtering every possible invalid character, how do you
convert dirty values without error. I am not concerned that I may loose
possibly valid values or convert suspect strings to 0 (zero). I just
want to run without raising an errorBooks Online is quite correct here, it just could be a bit clearer. The
significant word in the paragraph you posted is "or". ISNUMERIC returns
1 if the data is convertible to ANY of the datatypes listed. Try the
following, which will work:

SELECT CONVERT(MONEY, CHAR(9))

If you just want to convert positive integers then you can use LIKE to
determine whether a string contains only numerics:

SELECT CAST(col AS INTEGER)
FROM YourTable
WHERE col NOT LIKE '%[^0-9]%'

--
David Portas
SQL Server MVP
--|||By inference then CONVERT(MONEY, x) is the most 'tolerant' conversion.
Strings containing only numerics is also elegant.

Thanks for the tip|||(bilbo.baggins@.freesurf.ch) writes:
> By inference then CONVERT(MONEY, x) is the most 'tolerant' conversion.

Not necessarily:

SELECT convert(money, '1E1')

bombs. But isnumeric() returns 1.

isnumeric is a useless function.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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