0

I am dealing with a legacy database that is all kinds of messed up with its datatypes. There are columns that should be integers, but are saved as varchar(9). I am creating a new version of the table and trying to import the integer values properly. I have the following statement for the fields that need to be converted to int: 

CASE 
   ISNUMERIC(fieldname) 
       WHEN 0 THEN NULL 
       ELSE CAST(fieldname AS int) AS fieldname

The value that keeps tripping this up is: 029999E00.

The error is:

Conversion failed when converting the varchar value '029999E00' to type int.

Most of the time, this works great and will give me a valid int value or NULL.

Why is the ISNUMERIC saying this value is int, but then CAST is failing to convert it? I have tried bigint also and I get the same result. Is there another way to check for values like this?

There is another post about using ISNUMERIC as the solution, but ISNUMERIC doesn't work in this case. I need another option to check for a numeric value or to make sure the CAST statement won't fail when it gets the value if it's a Hex value.

Reuben Brown
  • 9
  • 4
  • 3
    The quick version is: `ISNUMERIC` is not a reliable way of determining if a value can be converted into an `INT`. What version of SQL Server are you using? – Siyual Apr 25 '16 at 18:41
  • 1
    Here is a great article explaining some of the pitfalls of ISNUMERIC. http://www.sqlservercentral.com/articles/ISNUMERIC%28%29/71512/ – Sean Lange Apr 25 '16 at 18:43
  • Are those values supposed to be hex? Because `ISNUMERIC` is interpreting your example as a scientific notation number `299999 * 10 ^ 0` – Stavr00 Apr 25 '16 at 19:40
  • @Stavr00 - The fields are supposed to be integer values but because it is fed in from another system that has no error handling, it gets garbage. – Reuben Brown Apr 25 '16 at 20:34
  • 1
    I may have found my solution from other link: CASE WHEN patindex('%[^0-9]%', fieldname)>0 This will check to see if there are non-numeric values and I can deal with the results from there. Thanks for the ideas. @Kamran Farzami - the article about IsNumeric was confirming what I already thought and found as an answer. – Reuben Brown Apr 25 '16 at 20:37

1 Answers1

1

Why not use TRY_CONVERT? If it can make an INT it will, otherwise it will return NULL.

Brian N
  • 11
  • 2
  • please use "Add Comment" to show your personal suggestion when you have enough reputation , thanks – Willie Cheng Apr 26 '16 at 03:53
  • @Brian-N : Great idea, but I am on MS SQL 2008 and that function was implemented in 2012 according to [MSDN](https://msdn.microsoft.com/en-us/library/hh230993.aspx). – Reuben Brown Apr 26 '16 at 16:58