Post reply

How to eliminate all NULL columns...

  • July 7, 2011 at 12:40 am

    #239200

    Hi All,

    I want to display records eliminating all NULL column records.

    I have around 50 columns which may have NULL values , Is there any way to handle this apart from the below one ??

    select * from table1 where ([names] is not null or [num] is not null or is not null or .....etc)

    Thanks

  • July 7, 2011 at 5:16 am

    This was removed by the editor as SPAM

  • July 7, 2011 at 10:20 am

    #1349813

    Something on the lines of this:

    select *

    from yourtable

    where ( col1 + col2 + col3 ....... + col50 ) is not null

    Concatenation of any value to a NULL yields NULL (only during aggregation fuctions NULLs are eliminated).

    HTH

  • July 7, 2011 at 7:41 pm

    #1350119

    If you take the below example,

    row 1 - fax column is null ,row 2&5 - e-mail and row 4 -mbl.

    Here i want to eliminate all these rows and display only the 3rd row which has no NULL value in any of the column...

    Name ------date---------size-------e-mail-----------fax--------mbl----------etc----

    aaa ------07/07/2007----34MB----aaa@aa.com------NULL-----99999999------etc----

    bbb ------01/01/2008----25MB-----NULL------------123456----991459999-----etc----

    ccc ------03/04/2008----15MB----ccc@cc.com------129356----99939999-----etc----

    ddd ------11/08/2009----10GB----ddd@dd.com------123456------NULL--------etc----

    eee ------02/05/2000----52MB-----NULL------------121456----99239999------etc----

  • July 7, 2011 at 9:22 pm

    #1350129

    did u try the way i posted?

  • July 7, 2011 at 9:42 pm

    #1350133

    thundersplash845 (7/7/2011)

    If you take the below example,

    row 1 - fax column is null ,row 2&5 - e-mail and row 4 -mbl.

    Here i want to eliminate all these rows and display only the 3rd row which has no NULL value in any of the column...

    Name ------date---------size-------e-mail-----------fax--------mbl----------etc----

    aaa ------07/07/2007----34MB----aaa@aa.com------NULL-----99999999------etc----

    bbb ------01/01/2008----25MB-----NULL------------123456----991459999-----etc----

    ccc ------03/04/2008----15MB----ccc@cc.com------129356----99939999-----etc----

    ddd ------11/08/2009----10GB----ddd@dd.com------123456------NULL--------etc----

    eee ------02/05/2000----52MB-----NULL------------121456----99239999------etc----

    Are any of the columns, perhaps such as the Name and Date columns, guaranteed to NOT be NULL?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 7, 2011 at 9:47 pm

    #1350135

    yes i did...but again ,have to define all columns in the where condition right??

    and moreover,columns do not have same datatype..

    Is there a way to check without specifying the column names ??

    Im not sure if it is possible to do by col/row check and ignore the NULL rows ...

    Any suggestion??

  • July 7, 2011 at 9:50 pm

    #1350136

    Jeff, Only name column will not hold any NULL value....date may have...

    Thanks!!

  • July 8, 2011 at 1:11 am

    This was removed by the editor as SPAM

  • July 8, 2011 at 7:08 am

    #1350335

    thundersplash845 (7/7/2011)

    Jeff, Only name column will not hold any NULL value....date may have...

    Thanks!!

    Then there's a possibility that this could be done rather "auto-magically" using dynamic SQL. Does the name column contain unique data?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 10, 2011 at 12:28 am

    #1350795

    Yeah, name column holds unique value.

  • July 11, 2011 at 9:43 am

    #1351171

    There isn't much room to improve over stewartc-708166's answer: all AND on(Col0-49 is not null). My app(C# & SQL) requirement test for all and group of columns, e.g subscriptions, geos.. Filter can occur in db or middle layer. Pseudo of my approach:

    add persisted computed column: hasValueFlags as cast(case when col0 is null then 0 else 0x1 end | .. | case when col49 is null then 0 else 0x2000000000000 end, as bigint) PERSISTED

    -- for column bitmask you can use pow(2,column ordinal position).

    -- add 1 or more non-clustered index to hasValueFlags all bits or partial.

    DECLARE @all50Cols bigint=0x3FFFFFFFFFFFF

    SELECT *

    FROM tbl

    WHERE @all50Cols =(hasValueFlags & 0xFFFFFFFFFFFFFFF)

Viewing 12 posts - 1 through 11 (of 11 total)

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