By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

But the documentation is NOT correct

P: n/a
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 error

Jul 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Books 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
--

Jul 23 '05 #2

P: n/a
By inference then CONVERT(MONEY, x) is the most 'tolerant' conversion.
Strings containing only numerics is also elegant.

Thanks for the tip

Jul 23 '05 #3

P: n/a
(bi***********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.