none
How to Query Multiple Columns for Null, Empty, and 0 Values

    Question

  • Hello All,

    I am trying to query a table's columns to check for null, 0, and empty values. I've been struggling with this and this is the latest thing I've tried:

    select

     

    st_nsn, dt_cycle

     FROM table

     

    WHERE

     

    ((Convert(DECIMAL, in_qty)) = 0 or in_qty = '' or in_qty IS NULL)

    OR

     

    ((Convert(Int, fl_item_wt) = 0) or fl_item_wt IS NULL or fl_item_wt = ''

    )

    OR

     

    ((Convert(Int, fl_item_cube) = 0 or fl_item_cube IS NULL or fl_item_cube = '')),

    but I'm still getting the errors, "Implicit conversion from datatype 'VARCHAR' to 'DECIMAL' is not allowed. Use the CONVERT function to run this query. Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query. Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query.", even though I'm using the Convert function. Any advice on this would be greatly appreciated.

     
    Thursday, September 15, 2011 8:28 PM

Answers

  • What are the types of in_qty and fl_item_wt and fl_item_cube fields?

    Assuming in prefix means integer and fl means float, you need

    where (in_qty = 0 or in_qty IS NULL)

    OR (fl_item_wt = 0 OR fl_item_wt IS NULL)

    OR (fl_item_cube = 0 OR fl_item_cube IS NULL)

     

    And don't check for '' if your values are numerical.

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by daniness Friday, September 16, 2011 3:46 PM
    Thursday, September 15, 2011 8:43 PM

All replies

  • What are the types of in_qty and fl_item_wt and fl_item_cube fields?

    Assuming in prefix means integer and fl means float, you need

    where (in_qty = 0 or in_qty IS NULL)

    OR (fl_item_wt = 0 OR fl_item_wt IS NULL)

    OR (fl_item_cube = 0 OR fl_item_cube IS NULL)

     

    And don't check for '' if your values are numerical.

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by daniness Friday, September 16, 2011 3:46 PM
    Thursday, September 15, 2011 8:43 PM
  • Hi,

    base on the error  'VARCHAR' to 'DECIMAL' / 'VARCHAR' to 'INT' ---- your data type is VARCHAR
    and you want the columns to check for null, 0, and empty values.

    try the code below:

    CREATE TABLE #T1(X INT,in_qty VARCHAR(20),fl_item_wt VARCHAR(20),fl_item_cube VARCHAR(20))
    
    
    INSERT INTO #T1
    SELECT 1,'','WOW','0'
    union
    SELECT 2,'','',''
    union 
    SELECT 3,'',NULL,'0'
    union 
    SELECT 4,'w',NULL,'0'
    
    
    select *
    FROM #T1
    WHERE
    (case 
    	when len(isnull(in_qty,0))=0 then 0 
    --	when isnumeric(in_qty)=1 then 1
    	else 1
    end)=0
    or 
    (case 
    	when len(isnull(fl_item_wt,0))=0 then 0 
    --	when isnumeric(fl_item_wt)=1 then 1
    	else 1
    end)=0
    or 
    (case 
    	when len(isnull(fl_item_cube,0))=0 then 0 
    --	when isnumeric(fl_item_cube)=1 then 1
    	else fl_item_cube
    end)=0
    

    ---- ANOTHER CONDITION

    select *
    FROM #T1
    WHERE 
    (isnull(len(in_qty),0)=0
    or
    isnull(len(fl_item_wt),0)=0
    or
    isnull(len(fl_item_cube),0)=0
    )






    DROP TABLE #T1




    Regards, Ryan Lambatan
    Please "Mark as Answer" or "Vote as Helpful"
    Thursday, September 15, 2011 11:43 PM
  • Wher is the DDL?

    Do not use CONVERT(); we have the ANSI/ISO Standard CAST() syntax now.

     


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Friday, September 16, 2011 1:31 AM
  • remember to use isnull(the_column,0) for every number and isnull(the_column,' ') for every string when referring to each column in the query
    Many Thanks & Best Regards, HuaMin Chen
    Friday, September 16, 2011 2:59 AM
  • You can also try -

    ISNULL(NULLIF(ColumnName,''),0) = 0


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Friday, September 16, 2011 5:56 AM
  • Thank you all for the replies!

    Naomi N, I understood yours, but how would I check for empty values, if you're advising to not check for ''?

    Friday, September 16, 2011 12:43 PM
  • Try using @NOAMI's approach and do consider @Vinay's & @HuaMin comments.

     

    As per @CELKO you should also provide table's and other objects DDLs with SQL Server version while posting questions in the forum. Check the top 3 posts in this forum.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Friday, September 16, 2011 12:53 PM
  • Can you please provide the DDL of the table? As I said, for numerical values you don't need to test for empty string (it is the same as checking for 0). If your columns are character, then you can test

     

    IF LEN(Field) > 0 -- this checks for empty and NULL
     AND Field <> '0'
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, September 16, 2011 1:04 PM
  • Hello All,

    Naomi, thanks a lot......I understand...there is no need to check for a string value of ' ' in the fields since none of the data types are string. I appreciate everyone's help! :-)

    Friday, September 16, 2011 3:46 PM