How to get column name whose value is not null in mysql

Question

I have a table which has a single entry. I have to get those column values whose values are not null. Please suggest me query for MySQL so I can implement this. My table is :

enter image description here

In this table 3 columns have Null values. So I don't want these columns, query should return values which in not null.

Can I get the column name also? Like I want to get name of the column i.e min_p5 whose value is not null. So I can break the column name into strings and use 5 in my calculation. Please suggest me answer.

Please use `SELECT` `CASE` in case check column value is `NOT NULL` then return column name otherwise return NULL — Girish, Feb 13 '14 at 07:39

score 2 · Answer 1 · edited May 23 '17 at 11:52

2

SELECT * 
FROM table 
WHERE YourColumn IS NOT NULL;

Source: MySQL SELECT only not null values

edited May 23 '17 at 11:52

Community

1
1

answered Feb 13 '14 at 07:37

Wesley Brian Lachenal

4,381
9
48
81

score 2 · Answer 2 · answered Feb 13 '14 at 07:38

2

select *  from table where column_name is not null

answered Feb 13 '14 at 07:38

Janak Prajapati

896
1
9
36

score 2 · Accepted Answer · answered Feb 13 '14 at 09:36

I think this is what you need:

Assuming your table name to be "orders" [pls change it accordingly]

$q="show columns from orders";
$res=mysql_query($q) or die(mysql_error());
$arr_field=array();
while($row=mysql_fetch_object($res)){
    $field=$row->Field;
    $q1="select ".$field." from orders where ".$field."!=0"; //if string then '0'
    $res1=mysql_query($q1) or die(mysql_error());
    if(mysql_num_rows($res1)>0){
        $arr_field[]=$field;
    }
}
$q="select ";
foreach($arr_field as $field){
    $q.=$field.",";
}
$q=rtrim($q,",");
$q.=" from orders";
$res=mysql_query($q) or die(mysql_error());
while($row=mysql_fetch_object($res)){
    foreach($arr_field as $field){
        print($field."==".$row->$field."<br/>");
    }
}

Run this and I hope you will get an idea...

score 1 · Answer 4 · answered Jul 15 '14 at 07:55

1

Try following query for solve your problem,

select * from table
where column_name IS NOT NULL

answered Jul 15 '14 at 07:55

Md. Mahabubur Rahman

790
1
7
21

score 0 · Answer 5 · answered Feb 13 '14 at 08:33

It seems like you want to return:

| POINT_ID | BUS_ID | MIN_P5 | MIN_P15 |
|----------|--------|--------|---------|
|     P101 |   B101 |   1000 |    3000 |

because you want to exclude columns that have a zero value. It is not too easy to do this in MySQL because you need to use prepared statements:

SELECT
  CONCAT(
    'SELECT CONCAT(\'SELECT \',
    CONCAT_WS(\',\',',
    GROUP_CONCAT(
    CONCAT(
      'CASE WHEN EXISTS(SELECT * FROM TABLENAME WHERE ',
      `column_name`,
      '!=\'0\') THEN \'',
      `column_name`,
      '\' END')
      ),
    '),\' FROM tablename\') FROM tablename INTO @final_sql'
  )
FROM   `information_schema`.`columns` 
WHERE  `table_schema`=DATABASE() 
       AND `table_name`='tablename'
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;
PREPARE finalstmt FROM @final_sql;
EXECUTE finalstmt;

Please see fiddle here. If your columns are numbers and not strings, maybe some minor fixes are needed. However, I would suggest you to try a different approach or to rethink about your table structure.

How to get column name whose value is not null in mysql

5 Answers5

Linked