Error converting data type varchar to numeric

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 7, 2013 at 2:01 am

  #1585264

  ~nano (2/7/2013)

  ---

  Hi there

  I had this frustrationg error on a query as well, all though the dataset I was using for a join didnt even contain anything other than numbers.

  I got it solved by using WHERE ISNUMERIC(<value>) = 1

  Don't know if it would help in your case, but it might be worth a shot.

  nano

  It's worth a shot - but it's also worth noting that ISNUMERIC() isn't all it seems. It roughly translates to "can this string be converted to any one of the numeric data types". In most cases folks will be converting to one specific datatype and ISNUMERIC may report false positives. Try this little code snippet:

  SELECT

  String,

  [ISNUMERIC] = ISNUMERIC(String),

  CASE WHEN String LIKE '%[0-9]%' THEN 1 ELSE 0 END,

  CASE WHEN String LIKE '%[0-9][0-9]%' THEN 1 ELSE 0 END

  FROM (

  SELECT String = '10' UNION ALL

  SELECT 'Letters' UNION ALL

  SELECT '1E1' UNION ALL

  SELECT '1E-9' UNION ALL

  SELECT 'nano' UNION ALL

  SELECT '1D1' UNION ALL

  select '+' UNION ALL

  select '-'

  ) d

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• ~nano

  SSC Eights!

  Points: 804

  More actions

  February 12, 2013 at 6:28 am

  #1586981

  Thank Chris

  In my case it worked -

  I had already had a clause to ensure a certain format etc. before casting/converting but the query just kept throwing the error

  even though the same query without casting/converting worked.

  After adding ISNUMERIC to the where clause the error wasnt raised anymore.

  But thanks all the more for the extra info on the function.

  nano