Error converting data type varchar

Question

I currently have a table with a column as varchar. This column can hold numbers or text. During certain queries I treat it as a bigint column (I do a join between it and a column in another table that is bigint)

As long as there were only numbers in this field had no trouble but the minute even one row had text and not numbers in this field I got a "Error converting data type varchar to bigint." error even if in the WHERE part I made sure none of the text fields came up.

To solve this I created a view as follows:

SELECT     TOP (100) PERCENT ID, CAST(MyCol AS bigint) AS MyCol
FROM         MyTable
WHERE     (isnumeric(MyCol) = 1)

But even though the view shows only the rows with numeric values and casts Mycol to bigint I still get a Error converting data type varchar to bigint when running the following query:

SELECT * FROM MyView where mycol=1

When doing queries against the view it shouldn't know what is going on behind it! it should simply see two bigint fields! (see attached image, even mssql management studio shows the view fields as being bigint)

Answer 1

Try using this:

SELECT 
  ID, 
  CAST(MyCol AS bigint) as MyCol
FROM
(
  SELECT TOP (100) PERCENT 
      ID, 
      MyCol 
  FROM 
      MyTable 
  WHERE 
      (isnumeric(MyCol) = 1)
) as tmp

This should work since the inner select only return numeric values and the outer select can therefore convert all values from the first select into a numeric. It seems that in your own code SQL tries to cast before executing the isnumeric function (maybe it has something to do with optimizing).

Answer 2

Try doing the select in 2 stages.

first create a view that selects all columns where my col is nummeric.

Then do a select in that view where you cast the varchar field.

The other thing you could look at is your design of tables to remove the need for the cast.

EDIT

• Are some of the numbers larger than bigint?
• Are there any spaces, leading, trailing or in the number?
• Are there any format characters? Decimal points?