How to replace NULL values with empty string in MySql Select ?

I had faced an issue in one of my project. When i was working with webservices. In that case i need to send empty string into response send NULL as value was not allowed and i had already seted NULL values as default fot many colums of tables. Also i dont want to interate the result and replace the null with empty string hence it slow down performance.

In that case i search the doc and got a three MySql functions :-

  1. COALESCE
  2. NULLIF
  3. IFNULL

There are two ways to achive this. In first case we can use COALESCE and NULLIF together and query will like this:-

SELECT COALESCE(NULLIF(ColumnName, ''), '') AS ColumnName FROM TABLE;

And in second case we can use IFNULL and query will look like

SELECT IFNULL(ColumnName, ”) AS ColumnName FROM TABLE;

And as i was using CodeIgniter as framework and using inbuilt active records. So, i used this as:-

$this->db->select(“COALESCE(NULLIF(ColumnName , ”), ”) AS ColumnName”, FALSE);

And for second case

$this->db->select(“IFNULL(ColumnName, ”) AS ColumnName”, FALSE);

This was the two way i found to achive this.

Leave a Reply

Your email address will not be published. Required fields are marked *