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))='';





39 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
  7. This comment has been removed by the author.

    ReplyDelete
  8. Nice! thank you so much! Thank you for sharing. Your blog posts are more interesting and informative. I think there are many people like and visit it regularly, including me. Digital Marketing Institute in Delhi | Digital Marketing Training in Delhi | Digital Marketing Training Institute in Delhi | Best Digital Marketing Institute in Delhi | Best Digital Marketing Course in Delhi

    ReplyDelete
  9. Extremely decent blog and articles. I am really extremely glad to visit your blog. Presently I am discovered which I really need. We are extremely thankful for your blog entry.
    Singing Classes in Delhi | Fashion Designing Course in Delhi | Makeup Course in Delhi | Event Management Course in Delhi | Interior Design Course in Delhi

    ReplyDelete
  10. I am thankful to this blog for assisting me. I added some specified clues which are really important for me to use them in my writing skill. Really helpful stuff made by this blog. Voip service

    ReplyDelete
  11. Thanks for sharing information.We also offer packers and movers in miyapur, Hyderabad and across all towns and cities in India.

    ReplyDelete
  12. You're the best for taking the time and composing this post. This post truly helped me to find out about this subject. Great post. On the off chance that you need you can visit my site to find out about style fit for lords (and, obviously, the sovereigns) and other such huge numbers of tips for better, cool, the kitschy item under the rainbow. thanks for sharing this comment.
    technology news pakistan

    ReplyDelete
  13. Today, Emblix as one of the best and top most service-oriented Digital Marketing Agency in Hyderabad and India , Which provides high-quality result-oriented Digital Services ranging from SEO to Web Design, Social Media Marketing and more, to a broad spectrum of clients from diverse industry segments. Through a well-oiled combination of Quality Solutions, Transparent Pricing, helping brands connect with customers, Flexible Delivery & Contract Model with a firm commitment to deliver on time and to budget, Emblix has successfully built a strong relationship with clients based on mutual trust and respect. Further, Emblix’s extensive market experience and expertise in Digital Marketing helps clients in successfully managing data as a strategic asset.


    Website: https://www.emblixsolutions.com

    ReplyDelete
  14. Thank you so much for this wonderful Post. This is an awesome post thank you for sharing this interesting post.

    7 tips to start a career in digital marketing

    “Digital marketing is the marketing of product or service using digital technologies, mainly on the Internet, but also including mobile phones, display advertising, and any other digital medium”. This is the definition that you would get when you search for the term “Digital marketing” in google. Let’s give out a simpler explanation by saying, “the form of marketing, using the internet and technologies like phones, computer etc”.

    we have offered to the advanced syllabus course digital marketing for available join now

    more details click the link now

    https://www.webdschool.com/digital-marketing-course-in-chennai.html

    ReplyDelete
  15. Thank for this blog are more informative step by step and useful contents. I here by attached my site would you see this blog.

    Web designing trends in 2020

    When we look into the trends, everything which is ruling today’s world was once a start up and slowly begun getting into. But Now they have literally transformed our lives on a tremendous note. To name a few, Facebook, Whats App, Twitter can be a promising proof for such a transformation and have a true impact on the digital world.

    we have offered to the advanced syllabus course web design and development for available join now

    more details click the link

    https://www.webdschool.com/web-development-course-in-chennai.html

    ReplyDelete
  16. Really awesome blog!!! I finally found a great post here.I really enjoyed reading this article. Thanks for sharing valuable information.
    Data Science Course

    ReplyDelete
  17. If you are stuck with your Law assignment then in this case you can opt for our Law Assignment Help. we provide the bestLaw homework writing service UK.We also provideBusiness Law Homework Help for students across the globe. for more information contact us +16692714848.

    ReplyDelete