Give the position of null values ​​from a table in MySQL

advertisements

I have a table with mixed of Null values and Non-Null values, I want position of the null value in the table for comparison before migration to other sources

mysql> select * from student2;
+------+---------+------------+
| id   | name    | address    |
+------+---------+------------+
|   10 | Rams    | NULL       |
|   12 | Hari    | NULL       |
|   13 | Shyam   | NULL       |
|   14 | NULL    | Hyderabad  |
|      | Revathy | Singapore  |

I am expecting output with only position of Null values along with the line number i.e

Row Number 1 Column Number 3
Row Number 2 Column Number 3
Row Number 3 Column Number 3
Row Number 4 Column Number 1
Row Number 5 Column Number 1


I think this query can help you:

SELECT (SELECT COUNT(*)
        FROM student2 si
        WHERE si.id <= s.id) As RowNo,
       CASE WHEN s.name IS NULL THEN 1
            WHEN s.address IS NULL THEN 2
       END As ColumnNo
FROM student2 s
WHERE COALESCE(s.name, s.address, '0') <> '0'