How to check if a column is empty or null in MySQL - MySQL Developer Tutorial

How to check if a column is empty or null in MySQL 

Let's say you have table with some column which has Null values and also blank values '',' ','    ' etc. You need to check if the column contain Null values or blank values, we can understand by using below example.

Let's create customer table and then insert some Null values in last name and blank values ('','    ') and then use select query to get Null values and blank values.


CREATE TABLE `customer` (
  `idcustomer` int(11) NOT NULL auto_increment,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL,
  Constraint pk_idcustomer Primary key (idcustomer)
) ;



Insert some values in customer table.


insert into customer
(firstname,lastname,age,phonenumber,dob,gender)
values
('Aamir1',null,39,'505-414000','1980-01-01','M'),
('Aamir2','',40,'505-4141000','1980-02-01','M'),
('Aamir3',' ',41,'505-4141000','1980-02-01','M');


Check the column if have null and empty values by using below query

Select * from customer 
where lastname is null or rtrim(ltrim(lastname))='';

No comments:

Post a Comment