Convert number vs isnumeric

Asked Oct 03 '13 at 22:44

Active Oct 04 '13 at 07:35

Viewed 101 times

This works and returns 1.

<cfquery name="test">
DECLARE ID varchar(50) = '8D4'

SELECT ISNUMERIC("8D4") AS isnum
</cfquery>

This fails

<cfquery name="test">
DECLARE ID varchar(50) = '8D4'

CASE WHEN ISNUMERIC(@ID) = 1 THEN CONVERT(bigint, @ID) ELSE NULL END AS Myid
</cfquery>

What kind of test should I perform to be confidant that the CONVERT will work?

edited Oct 04 '13 at 07:35

Adam Cameron

asked Oct 03 '13 at 22:44

James A Mohler

2

see [this question](http://stackoverflow.com/questions/5988939/isnumeric07213e71-true) for an explanation and [this question](http://stackoverflow.com/questions/2358147/best-equivalent-for-isinteger-in-sql-server) for a solution – T I Oct 03 '13 at 23:08
There are plenty of suggestions if you google "sql server isnumeric E and D". This has a few options, just on the one page: http://www.sqlservercentral.com/Forums/Topic472175-9-1.aspx – Adam Cameron Oct 04 '13 at 07:35

0 Answers0