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

All replies

  • The following link may be helpful.

    http://stackoverflow.com/questions/8234964/how-to-remove-every-empty-row-from-any-sql-table

    Rajitha


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

    Tuesday, April 17, 2012 4:58 AM
  • delete from @tmpTable
    where trim(name)=''
    and trim(address1)=''
    and trim(phnno)=''
    and tri(mobno)='';

    Many Thanks & Best Regards, Hua Min

    Tuesday, April 17, 2012 5:03 AM
  • Hello Rajitha.S

    Thanks for the help it helped me but have to do some modification to achieve the result.

    Below are the modification.

    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'
    
    
    ;with C(XmlCol) as
    (
      select
        (select ltrim(rtrim(T.name)) as name,ltrim(rtrim(T.address1)) as address1,
    			ltrim(rtrim(T.phnno)) as phnno,ltrim(rtrim(T.mobno)) as mobno
         for xml path('row'), type)
      from @tmpTable as T
    )
    delete from C
    where C.XmlCol.exist('row/*[. != ""]') = 0
    
    SELECT * FROM @tmpTable

    Thanks & Regards

    Tuesday, April 17, 2012 7:43 AM
  • ;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
  • Parameterized version I use copied below ( also posted to http://stackoverflow.com/questions/8234964/how-to-remove-every-empty-row-from-any-sql-table/32320963#32320963 ). Hope it helps anyone coming after

    create proc dbo.spDeleteRowsWhereAllColsAreNull
        @Schema nvarchar( 116 ),
        @Table nvarchar( 116 )
    as
    begin
        declare
                @RetMsg nvarchar( max ),
                @CountRows int,
                @ProcName nvarchar( 255 ) = N'dbo.spDeleteRowsWhereAllColsAreNull',
                @DynamicSql nvarchar( max ) = N'',
                @Schema_Table nvarchar( 255 ) = @Schema + N'.' + @Table,
                @Column nvarchar( 116 ),
                @Lb nchar( 1 ) = char( 13 ),
                @Tab nchar( 1 ) = char( 9 )
    
    -- Check if target exists, else escape proc
        if exists( select * 
                    from sys.tables
                    where [object_id] = object_id( @Schema_Table ) )
        begin
            select @DynamicSql = 'delete from ' + @Schema_Table + N' where ' ;
    
    -- Get all columns for target table into @DynamicSql
            select @DynamicSql += 
                @Tab + name+ N' is null and ' + @Lb
            from sys.columns
            where [object_id] = object_id( @Schema_Table ) ;
    
            set @DynamicSql = left( @DynamicSql, len( @DynamicSql ) - 6 ) ;
            -- print @DynamicSql ;
    
            exec sp_executesql @DynamicSql ;
            set @CountRows = @@rowcount ;
            set @RetMsg = @ProcName + N' executed in current Database ' + db_name() + N' on table ' + @Schema_Table + N'. ' + convert( nvarchar, @CountRows ) + N' row(s) deleted.' ;
    
    -- Print results & return success
            print @RetMsg ;
            return 0 ;
        end
        else
        begin
    -- Raiserror & return failure
            set @RetMsg = @Schema_Table + N' does not exist current Database ' + db_name() + N'. Execution FAILED!';
            raiserror( @RetMsg, 11, -1 ) ;
            return -1 ;
        end ;
    
    end ;
    go


    Monday, August 31, 2015 10:41 PM