Error Converting Varchar to Numeric

Posted on 2011-04-29
Last Modified: 2012-06-27

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.

Question by:JohnJMA
    LVL 9

    Expert Comment

    You need to update this manually or You have to set a trigger to change this value when a record was inserting.
    LVL 75

    Accepted Solution

    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

    Author Closing Comment

    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

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now