none
How to remove NULL in column

    Question

  • Dear All,


    May i know how to remove the null in column ? could i use case when condition ?


    Monday, September 30, 2013 3:24 AM

Answers

  • What you mean by removing null in column?

    * If you don't want any records with null value then

    ex:select * from table where columnname is not null

    * If you don't want NULL value then use ISNULL function

    ex:select col1,isnull(col2,''),isnull(col3,'') from table


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    • Marked as answer by Caulson Monday, September 30, 2013 5:22 AM
    Monday, September 30, 2013 4:16 AM
  • Are you looking for the below:

    select EP_SHIFT,ISNULL(LEAVE_NO,0),ISNULL(SUM_DAYS,0) 
    from tblName


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Caulson Monday, September 30, 2013 5:22 AM
    Monday, September 30, 2013 4:47 AM

All replies

  • Hi

    you can write your query like:

    select * from tblName where SUM_DAYS is NOT NULL;
    Hope it gives you a basic idea. 

    Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you.

    Monday, September 30, 2013 3:32 AM
  • What you mean by removing null in column?

    * If you don't want any records with null value then

    ex:select * from table where columnname is not null

    * If you don't want NULL value then use ISNULL function

    ex:select col1,isnull(col2,''),isnull(col3,'') from table


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    • Marked as answer by Caulson Monday, September 30, 2013 5:22 AM
    Monday, September 30, 2013 4:16 AM
  • 1) If you want to remove NULL values from the table and wanting to replace with blank space, you can do that with update query

    2) If you want to remove NULL values from the returned result set you can use ISNULL function or case statement if null value then ' ' in your selected statement

    if the value is null then you can select blank space



    Mark ANSWER if this reply resolves your query, If helpful then VOTE HELPFUL
    Everything about SQL Server | Experience inside SQL Server -Mohammad Nizamuddin

    Monday, September 30, 2013 4:38 AM
  • Are you looking for the below:

    select EP_SHIFT,ISNULL(LEAVE_NO,0),ISNULL(SUM_DAYS,0) 
    from tblName


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Caulson Monday, September 30, 2013 5:22 AM
    Monday, September 30, 2013 4:47 AM
  • use AdventureWorks2012
    go
    
    /* Three ways to replace Null values with ''.*/
    select  case 
    		  when DocumentSummary is null then '' 
    		  else DocumentSummary 
    		end DocumentSummary1,
    		isnull(DocumentSummary, '') DocumentSummary2,
    		coalesce(DocumentSummary, '') DocumentSummary3
    from 
    	Production.Document;
    
    
    /* condition to eliminate Null values.*/
    select 
    	DocumentSummary
    from 
    	Production.Document
    where 
    	DocumentSummary is not null;


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Monday, September 30, 2013 4:49 AM
  • Try

    update tab1
    set col1=case when col1 is null then '' else col1 end


    Many Thanks & Best Regards, Hua Min

    Monday, September 30, 2013 4:59 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    Your useless picture has no key and no way to have a key. Try again. 

    --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

    Monday, September 30, 2013 3:52 PM