Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • 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.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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