SQL Server error converting data type varchar to numeric

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql-server

Examples related to numeric

Examples related to varchar