• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Error Converting Varchar to Numeric


I am getting an error when trying to compare a varchar value to a numeric value.  My question is there a what to force the varchar value to a defalut numeric value?  For example, if the value is "Misc" I would like that value to default to 9999 which in the Join table does exist as an "Unknown" code.  And if the value is '123' this is a valid number in the Join table and should be found.  

Thanks for the assistance.

1 Solution
You need to update this manually or You have to set a trigger to change this value when a record was inserting.
Anthony PerkinsCommented:
This is going to be an extremely ugly JOIN, but if you insist you can do something like this:
FROM Table1 t1
          INNER JOIN Table2 t2 ON CASE t1.YourVarcharColumn WHEN 'Misc' THEN 9999 ELSE CAST(t1.YourVarcharColumn AS integer) END = t2.YourNumericColumn
JohnJMAAuthor Commented:
You are right it is extrememly ugly.  It works for now but I am going to make a perminante solution and change the database.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now