MySql replace NULL values with empty string

Date Posted: 05-04-2018

In this we will explain MySql replace NULL values with empty string.

Step1: You can create two different tables. using the following query.

CREATE TABLE `users` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `username` varchar(16) NOT NULL,
 `name` varchar(40) DEFAULT NULL,
 `last_name` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `users`(username,name,last_name) VALUES ('xxx','xyz','abc');

CREATE TABLE `billings` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(11) NOT NULL,
 `address` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

INSERT INTO `billings`(user_id,address) VALUES ('1','');

Step2: We can use mysql IFNULL function to replace the null values with empty string.

Synatx:

IFNULL(null,"") as value 

Example:

SELECT u.*,IFNULL(b.address,"") AS address FROM `users` as u LEFT JOIN billings as b on b.user_id=u.id  order by u.id