Professional Web Applications Themes

find null value in any column - MySQL

Hi, Assume I have a table with only 1 row and all column is 'int'. I just wonder is it possible to use SQL to check if any one of those column contain a 'null' without knowing the column name? For example, create t1 ( i1 int null, i2 int null ....) insert into t1 (i1, i2, ...) values (1, 1, 1, ... , null, 1, ... null, ...) and check if any of those column contain null... Have fun, Willie...

  1. #1

    Default find null value in any column

    Hi,

    Assume I have a table with only 1 row and all column is 'int'. I just
    wonder is it possible to use SQL to check if any one of those column
    contain a 'null' without knowing the column name?

    For example,

    create t1 ( i1 int null, i2 int null ....)
    insert into t1 (i1, i2, ...) values (1, 1, 1, ... , null, 1, ... null,
    ...)

    and check if any of those column contain null...


    Have fun,
    Willie

    wilson.sh.tam@gmail.com Guest

  2. #2

    Default Re: find null value in any column


    [email]wilson.sh.tam[/email] wrote:


    > Assume I have a table with only 1 row and all column is 'int'. I just
    > wonder is it possible to use SQL to check if any one of those column
    > contain a 'null' without knowing the column name?
    > For example,
    > create t1 ( i1 int null, i2 int null ....)
    > insert into t1 (i1, i2, ...) values (1, 1, 1, ... , null, 1, ... null,
    > ..)
    > and check if any of those column contain null...

    Select from t1 where i1 is null or i2 is null or ....... in is null



    Paul...




    > Have fun,
    > Willie
    --

    plinehan __at__ yahoo __dot__ __com__

    XP Pro, SP 2,

    Oracle, 9.2.0.1.0 (Enterprise Ed.)
    Interbase 6.0.1.0;

    When asking database related questions, please give other posters
    some clues, like operating system, version of db being used and DDL.
    The exact text and/or number of error messages is useful (!= "it didn't work!").
    Thanks.

    Furthermore, as a courtesy to those who spend
    time ysing and attempting to help, please
    do not top post.
    Paul Guest

  3. #3

    Default Re: find null value in any column

    Hi Paul,

    Thanks for your reply. Yes, I should have mention this. The databases
    are mysql 5.0.22 and postgresql v7.4.13. I am running them on FreeBSD
    6.1 release box.

    Paul wrote:
    > [email]wilson.sh.tam[/email] wrote:
    >
    >
    >
    > > Assume I have a table with only 1 row and all column is 'int'. I just
    > > wonder is it possible to use SQL to check if any one of those column
    > > contain a 'null' without knowing the column name?
    >
    > > For example,
    >
    > > create t1 ( i1 int null, i2 int null ....)
    > > insert into t1 (i1, i2, ...) values (1, 1, 1, ... , null, 1, ... null,
    > > ..)
    >
    > > and check if any of those column contain null...
    >
    >
    > Select from t1 where i1 is null or i2 is null or ....... in is null
    >
    >
    >
    > Paul...
    >
    >
    >
    >
    >
    > > Have fun,
    > > Willie
    >
    > --
    >
    > plinehan __at__ yahoo __dot__ __com__
    >
    > XP Pro, SP 2,
    >
    > Oracle, 9.2.0.1.0 (Enterprise Ed.)
    > Interbase 6.0.1.0;
    >
    > When asking database related questions, please give other posters
    > some clues, like operating system, version of db being used and DDL.
    > The exact text and/or number of error messages is useful (!= "it didn't work!").
    > Thanks.
    >
    > Furthermore, as a courtesy to those who spend
    > time ysing and attempting to help, please
    > do not top post.
    wilson.sh.tam@gmail.com Guest

  4. #4

    Default Re: find null value in any column



    [email]wilson.sh.tam[/email] wrote:

    > Thanks for your reply. Yes, I should have mention this. The databases
    > are mysql 5.0.22 and postgresql v7.4.13. I am running them on FreeBSD
    > 6.1 release box.

    Please do not top-post.
    > > Select from t1 where i1 is null or i2 is null or ....... in is null

    That of course should be Select <my_field_list> from t1... &c.


    I don't think the platform should matter that much for this query - it
    is more important when talking about performance/installation issues.


    BTW, just as a matter of interest, how is FreeBSD 6.1?


    Paul...


    --

    plinehan __at__ yahoo __dot__ __com__

    XP Pro, SP 2,

    Oracle, 9.2.0.1.0 (Enterprise Ed.)
    Interbase 6.0.1.0;

    When asking database related questions, please give other posters
    some clues, like operating system, version of db being used and DDL.
    The exact text and/or number of error messages is useful (!= "it didn't work!").
    Thanks.

    Furthermore, as a courtesy to those who spend
    time ysing and attempting to help, please
    do not top post.
    Paul Guest

  5. #5

    Default Re: find null value in any column

    If you know the number of columns of your table t1.
    Though it may a little troublesome, it is easy.

    But, if you don't know the number of columns or you want to make it
    generally.
    I think you should check System Catalog or any other way(I think all
    DBMS have some way to know column names, column sequences and other
    attributes) to know the number of columns. Then dynamically construct
    SQL statement.

    For example:
    Assume t1 have 7 columns.

    SELECT SUBSTR('No NULL NULL exist',1+10*SIGN(COUNT(*)-COUNT(t1)),10)
    "Check NULL"
    FROM (SELECT CASE n
    WHEN 1 THEN c1
    WHEN 2 THEN c2
    WHEN 3 THEN c3
    WHEN 4 THEN c4
    WHEN 5 THEN c5
    WHEN 6 THEN c6
    WHEN 7 THEN c7
    END AS t1
    FROM (SELECT * FROM t1) S (c1,c2,c3,c4,c5,c6,c7)
    , (VALUES 1, 2, 3, 4, 5, 6, 7) P (n)
    ) T (t1)
    ;

    The result will be:
    1)
    Check NULL
    ----------
    NULL exist

    1 record(s) selected.

    or
    2)
    Check NULL
    ----------
    No NULL

    1 record(s) selected.

    Tonkuma Guest

  6. #6

    Default Re: find null value in any column

    [email]wilson.sh.tam[/email] wrote:
    >> just wonder is it possible to use SQL to check if any one of those column contain a 'null' without knowing the column name? <<
    Paul's answer
    "Select from t1 where i1 is null or i2 is null or ....... in is null"
    need to know column names(i1, i2, ..., in).

    wilson,
    You wrote "without knowing the column name". I want ask you the Paul's
    answer satisfy your requirements?

    Tonkuma Guest

  7. #7

    Default Re: find null value in any column

    >> Assume I have a table with only 1 row and all column is INTEGER. I just wonder is it possible to use SQL to check if any one of those column contain a 'null' without knowing the column name? <<

    SELECT 'yes'
    FROM Foobar
    WHERE (c1 + c2 + c3+ .. + cn) IS NULL;

    --CELKO-- Guest

  8. #8

    Default Re: find null value in any column

    On 18 Jul 2006 09:33:20 -0700, "--CELKO--" <jcelko212earthlink.net>
    wrote:
    >>> Assume I have a table with only 1 row and all column is INTEGER. I just wonder is it possible to use SQL to check if any one of those column contain a 'null' without knowing the column name? <<
    >
    >SELECT 'yes'
    > FROM Foobar
    >WHERE (c1 + c2 + c3+ .. + cn) IS NULL;
    <OT>
    Joe, is that you?

    Remember the Watcom Compuserve forums?
    </OT>

    --
    Al Balmer
    Sun City, AZ
    Al Balmer Guest

Similar Threads

  1. Adding UNIQUE constraint on NULL column
    By Dave Smith in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: January 13th, 09:39 PM
  2. Null values in a datagrid checkbox column
    By Rock in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: December 3rd, 02:26 AM
  3. How to ensure if column A is null, column B has to be null
    By Bill in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 07:09 PM
  4. Have trigger supply value for NOT NULL column on insert
    By Ian Boyd in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 3rd, 02:59 PM
  5. Autoincrementing a column for rows with null
    By Jason in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 11:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139