[sql-server] SQL Server : error converting data type varchar to numeric

I have a table:

Account_Code | Desc
503100       | account xxx
503103       | account xxx
503104       | account xxx
503102A      | account xxx
503110B      | account xxx

Where Account_Code is a varchar.

When I create a query below:

Select 
  cast(account_code as numeric(20,0)) as account_code,
  descr 
from account 
where isnumeric(account_code) = 1

It runs well by returning all record that have a valid numeric value in account_code column.

But when I try to add another select, nested to prior sql:

select account_code,descr 
from 
(
  Select cast(account_code as numeric(20, 0)) as account_code,descr 
  from account 
  where isnumeric(account_code) = 1
) a 
WHERE account_code between 503100 and 503105

the query will return an error

Error converting data type varchar to numeric.

What is happening there?

I have already converted to numeric if account_code valid, but it seems the query is still trying to process a non valid record.

I need to use BETWEEN clause in my query.

There's no guarantee that SQL Server won't attempt to perform the CONVERT to numeric(20,0) before it runs the filter in the WHERE clause.

And, even if it did, ISNUMERIC isn't adequate, since it recognises £ and 1d4 as being numeric, neither of which can be converted to numeric(20,0).(*)

Split it into two separate queries, the first of which filters the results and places them in a temp table or table variable, the second of which performs the conversion. (Subqueries and CTEs are inadequate to prevent the optimizer from attempting the conversion before the filter)

For your filter, probably use account_code not like '%[^0-9]%' instead of ISNUMERIC.

(*) ISNUMERIC answers the question that no-one (so far as I'm aware) has ever wanted to ask - "can this string be converted to any of the numeric datatypes - I don't care which?" - when obviously, what most people want to ask is "can this string be converted to x?" where x is a specific target datatype.

SQL Server 2012 and Later

Just use Try_Convert instead:

TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.

Read more about Try_Convert.

SQL Server 2008 and Earlier

The traditional way of handling this is by guarding every expression with a case statement so that no matter when it is evaluated, it will not create an error, even if it logically seems that the CASE statement should not be needed. Something like this:

SELECT
   Account_Code =
      Convert(
         bigint, -- only gives up to 18 digits, so use decimal(20, 0) if you must
         CASE
         WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
         ELSE X.Account_Code
         END
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      CASE
      WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
      ELSE X.Account_Code
      END
   ) BETWEEN 503100 AND 503205

However, I like using strategies such as this with SQL Server 2005 and up:

SELECT
   Account_Code = Convert(bigint, X.Account_Code),
   A.Descr
FROM
   dbo.Account A
   OUTER APPLY (
      SELECT A.Account_Code WHERE A.Account_Code NOT LIKE '%[^0-9]%'
   ) X
WHERE
   Convert(bigint, X.Account_Code) BETWEEN 503100 AND 503205

What this does is strategically switch the Account_Code values to NULL inside of the X table when they are not numeric. I initially used CROSS APPLY but as Mikael Eriksson so aptly pointed out, this resulted in the same error because the query parser ran into the exact same problem of optimizing away my attempt to force the expression order (predicate pushdown defeated it). By switching to OUTER APPLY it changed the actual meaning of the operation so that X.Account_Code could contain NULL values within the outer query, thus requiring proper evaluation order.

You may be interested to read Erland Sommarskog's Microsoft Connect request about this evaluation order issue. He in fact calls it a bug.

There are additional issues here but I can't address them now.

P.S. I had a brainstorm today. An alternate to the "traditional way" that I suggested is a SELECT expression with an outer reference, which also works in SQL Server 2000. (I've noticed that since learning CROSS/OUTER APPLY I've improved my query capability with older SQL Server versions, too--as I am getting more versatile with the "outer reference" capabilities of SELECT, ON, and WHERE clauses!)

SELECT
   Account_Code =
      Convert(
         bigint,
         (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
   ) BETWEEN 503100 AND 503205

It's a lot shorter than the CASE statement.

thanks, try this instead

Select 
  STR(account_code) as account_code_Numeric,
  descr 
from account 
where  STR(account_code) = 1

I'm happy to help you

If you are running SQL Server 2012 or newer you can also use the new TRY_PARSE() function:

Returns the result of an expression, translated to the requested data type, or null if the cast fails in SQL Server. Use TRY_PARSE only for converting from string to date/time and number types.

Or TRY_CONVERT/TRY_CAST:

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

I think the problem is not in sub-query but in WHERE clause of outer query. When you use

WHERE account_code between 503100 and 503105

SQL server will try to convert every value in your Account_code field to integer to test it in provided condition. Obviously it will fail to do so if there will be non-integer characters in some rows.