none
Remove Blank Rows from SQL server Table

    Question

  • Hello,

    I have a table for example like following

    DECLARE @tmpTable table
    (
    	name varchar(10),
    	address1 varchar(10),
    	phnno varchar(10),
    	mobno varchar(10)
    )
    
    INSERT INTO @tmpTable(name,address1,phnno,mobno)
    SELECT 'av','aba','2112','13213'
    UNION ALL
    SELECT '','','',''
    UNION ALL
    SELECT ' ',' ',' ',' '
    UNION ALL
    SELECT NULL,NULL,NULL,NULL
    UNION ALL
    SELECT 'av','aba','2112','13213'
    

    I want to remove all empty rows like row 1,2 and 3 in the above example.

    I can't check all columns null values as there are many columns in my actual table.

    Please help me reach this solution.

    Thank & Regards.


    • Changed type Naomi NModerator Tuesday, April 17, 2012 10:12 AM Question rather than discussion
    Tuesday, April 17, 2012 4:52 AM

Answers

  • ;with cte as 
    (
    select *,name+address1 +phnno +mobno as r from @tmpTable 
    )
    
    delete  from cte where r IS  NULL OR  r=''
    select * from @tmpTable 


    Thanks and regards, Rishabh , Microsoft Community Contributor

    • Proposed as answer by skc_chat Tuesday, April 17, 2012 10:44 AM
    • Marked as answer by KJian_ Monday, April 23, 2012 6:06 AM
    Tuesday, April 17, 2012 9:01 AM

All replies