- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can we apply COALESCE() function on a MySQL table’s data value?
152 Lectures 16 hours
87 Lectures 5.5 hours
If we want to apply COALESCE() function on a MySQL table’s data value then we need to use the name of the columns as the argument of this function. If there would be a NULL value in the first column then it will check for the next column and so on until it gets non-NULL value. We are using the data from ‘employee’ table, as follows, to demonstrate the above concept −
mysql> Select * from employee; +----+------------+-------------+-----------+ | Id | First_Name | Middle_Name | Last_Name | +----+------------+-------------+-----------+ | 1 | Advik | NULL | Jhamb | | 2 | Rahul | Kumar | Sharma | | 3 | Raman | Singh | Rajput | | 4 | Krishan | Kumar | Khurana | | 5 | Sachin | Ramesh | Tendulkar | | 6 | NULL | Kumar | Gaurav | | 7 | NULL | Singh | Parmar | +----+------------+-------------+-----------+ 7 rows in set (0.00 sec) mysql> Select COALESCE(First_Name, Middle_Name,Last_Name)AS Name FROM Employee; +---------+ | Name | +---------+ | Advik | | Rahul | | Raman | | Krishan | | Sachin | | Kumar | | Singh | +---------+ 7 rows in set (0.03 sec)
In the above example, there are three arguments to COALESCE() function and above query returns name from the First_Name, Middle_Name and Last_Name and its returns value of Middle_Name if First_Name have NULL. Then same for Last_Name, it returns Last_Name value if First_Name and Middle_Name have NULL. It returns NULL if all three First_Name, Middle_Name and Last_Name are NULL.
- How can we apply BIT_LENGTH() function on the column/s of MySQL table?
- How can we insert data into a MySQL table?
- How can we use MySQL REVERSE() function on column’s data along with WHERE clause?
- How can we modify column/s of MySQL table?
- How can we copy data with some condition/s from existing MySQL table?
- How to apply EXTRACT() function on the dates stored in MySQL table?
- How can we use MySQL POWER() function with the column’s data values?
- How can we create a MySQL stored function that uses the dynamic data from a table?
- How can we apply a NOT NULL constraint to a column of an existing MySQL table?
- How can I insert a value in a column at the place of NULL using MySQL COALESCE() function?
- How can we apply UNIQUE constraint to the field of an existing MySQL table?
- How can we use MySQL EXPORT_SET() function with column of a table?
- How can MySQL COALESCE() function be used with MySQL SUM() function to customize the output?
- How can we make a MySQL clone table?
- How can we export some field(s) from MySQL table into a text file?