Null and Blank values in MySQL - MySQL Developer Tutorial

Null and Blank values in MySQL

Null is absence of value. The blank is value that is empty string. Let's say if we need to create customer table with different columns. There is possibility that sometime we don't have any last name. So we will leave the column without inserting any value, As we are not inserting any value in last name then it will have null ( Absence of value). 

There could be scenarios when you will insert the value but blank. Let's create our table and then insert leave few column value as Null and insert some blank values.


CREATE TABLE `customer` (
  `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
) ;


Let's insert some values in customer table.

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Aamir','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Bob',Null,59,'505-4141969','1980-01-01','M'),
(4,'Robert','',59,'505-4141969','1980-01-01','M'),
(5,'Lisa','   ',59,'505-4141969','1980-01-01','M');

Now if you want to get the records from customer table which don't have value (absent of value) in lastname column. you can use below query.

select * from customer where lastname is NULL;


If we need to find all the rows which has blank value in lastname column, and you can see in our insert statement we have blank values which is equal to '' or '  ' or more blank spaces.

We can use ltrim and rtrim to trim to single blank space to compare and get all the records.

select * from customer 
where Rtrim(ltrim(lastname))='';





8 comments:

  1. They're produced by the very best degree developers who will be distinguished for your polo dress creating. You'll find polo Ron Lauren inside exclusive array which include particular classes for men, women.
    artificial intelligence course in india

    ReplyDelete
  2. There are such a large number of kinds of articles accessible. I extremely get a kick out of the chance to read the one which you have partaken in this post. Each article has novel and particular characteristics to its write. We can see some same characters in all articles. Writers motivate opportunity to share imperative and convincing data by inquiring about and framing writing thought and altering work to the readers. You have completed an incredible work. I truly making the most of your posts. Refer best essay writing service to get any help in writing.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. I like your post very much. It is very much useful for my research. I hope you to share more info about this. Keep posting SQL server DBA certification

    ReplyDelete

  5. الرائد افضل شركات تنظيف خزانات المياه يسعدنا ان نقدم لكم افضل خدمات
    شركة غسيل خزانات بالمدينة المنورة تنظيف خزانات بالمدينة المنورة
    افضل شركة تنظيف منازل بالمدينة المنورة شركة تنظيف بيوت بالمدينة المنورة

    ReplyDelete
  6. I am looking for SQL commands and this will be very helpful for me to learn more about table insertion.

    Digital marketing

    ReplyDelete