Post reply

Numeric/Negative Check

  • July 17, 2015 at 1:28 am

    #309941

    i Have a table with a column Capacity which is char(10) and gets populated from user files. I want to check records which have negative Capacity value. So i first checked if its numeric and then for negative.

    select * from table WHEREISNUMERIC(LTRIM(RTRIM(Capacity))) = 1 AND Capacity < 0

    BUT still it checks for char fields too giving errors like - Conversion failed when converting the varchar value 'asdf ' to data type int.

    Please help

  • July 17, 2015 at 1:34 am

    #1813173

    Is it fine if you check for the 1st character alone. If it is - then, negative value..

    LEFT(Name, 1) ='-'

  • July 17, 2015 at 3:04 am

    #1813194

    Yes. Thanks. It does work this way.. but dont we have any other way to check for < 0 in sql?

  • July 17, 2015 at 12:52 pm

    #1813403

    Because SQL is a declarative language you can't enforce which criteria is evaluated first, the optimizer chooses based on what it thinks will work fastest. The other thing you need to remember is that ISNUMERIC() doesn't guarantee that the value evaluated is a number. It can be several other characters like $. Check out this article, http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/ which also gives a starting point for a solution to your issue.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • July 17, 2015 at 1:01 pm

    #1813408

    nidhi.naina (7/17/2015)

    i Have a table with a column Capacity which is char(10) and gets populated from user files. I want to check records which have negative Capacity value. So i first checked if its numeric and then for negative.

    select * from table WHEREISNUMERIC(LTRIM(RTRIM(Capacity))) = 1 AND Capacity < 0

    BUT still it checks for char fields too giving errors like - Conversion failed when converting the varchar value 'asdf ' to data type int.

    Please help

    Since you posted on a SQL 2005 forum, I'd like to verify that this is the version that you are using.

    If you are using 2012 or higher, you could use the TRY_CONVERT function to convert to int, and then check.

    WHERE TRY_CONVERT(INTEGER, Capacity) < 0

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • July 17, 2015 at 1:14 pm

    #1813413

    And this is the problem with cross posting. We don't know if you're using 2005 or 2012 as you posted basically the same question on both forums and got similar replies.

    http://www.sqlservercentral.com/Forums/Topic1703693-3077-1.aspx

    Please post only on the appropriate forum and don't cross post as you only divide your answers.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply