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





68 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
  18. It's very useful blog post with inforamtive and insightful content and i had good experience with this information. We, at the CRS info solutions ,help candidates in acquiring certificates, master interview questions, and prepare brilliant resumes.Find top Salesforce admin interview questions in 2020.
    These Salesforce developer interview questions are highly helpful in 2020. You can read these Salesforce lightning interview questions and Salesforce integration interview questions which are prepared by industry experts.

    ReplyDelete
  19. I am impressed. I don't think Ive met anyone who knows as much about this subject as you do. You are truly well informed and very intelligent. You wrote something that people could understand and made the subject intriguing for everyone. Really, great blog you have got here. Avenue South Residence

    ReplyDelete
  20. Really I enjoy your site with effective and useful information. It is included very nice post with a lot of our resources.thanks for share. i enjoy this post. Avenue South Residence

    ReplyDelete
  21. I am impressed. I don't think Ive met anyone who knows as much about this subject as you do. You are truly well informed and very intelligent. You wrote something that people could understand and made the subject intriguing for everyone. Really, great blog you have got here. The Avenir

    ReplyDelete
  22. Really I enjoy your site with effective and useful information. It is included very nice post with a lot of our resources.thanks for share. i enjoy this post. The Avenir

    ReplyDelete
  23. I am impressed. I don't think Ive met anyone who knows as much about this subject as you do. You are truly well informed and very intelligent. You wrote something that people could understand and made the subject intriguing for everyone. Really, great blog you have got here. midtown modern condo

    ReplyDelete
  24. Really I enjoy your site with effective and useful information. It is included very nice post with a lot of our resources.thanks for share. i enjoy this post. midtown modern condo

    ReplyDelete

  25. Through this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing on Msbi online training
    Msbi online training Hyderabad
    Msbi online training India
    Msbi online course

    ReplyDelete
  26. I am impressed. I don't think Ive met anyone who knows as much about this subject as you do. You are truly well informed and very intelligent. You wrote something that people could understand and made the subject intriguing for everyone. Really, great blog you have got here. myra condo

    ReplyDelete
  27. Really I enjoy your site with effective and useful information. It is included very nice post with a lot of our resources.thanks for share. i enjoy this post. myra condo

    ReplyDelete
  28. I am impressed. I don't think Ive met anyone who knows as much about this subject as you do. You are truly well informed and very intelligent. You wrote something that people could understand and made the subject intriguing for everyone. Really, great blog you have got here. One Pearl Bank

    ReplyDelete
  29. Really I enjoy your site with effective and useful information. It is included very nice post with a lot of our resources.thanks for share. i enjoy this post. One Pearl Bank

    ReplyDelete
  30. I am impressed. I don't think Ive met anyone who knows as much about this subject as you do. You are truly well informed and very intelligent. You wrote something that people could understand and made the subject intriguing for everyone. Really, great blog you have got here. Hyll On Holland

    ReplyDelete
  31. Really I enjoy your site with effective and useful information. It is included very nice post with a lot of our resources.thanks for share. i enjoy this post. Hyll On Holland

    ReplyDelete


  32. Through this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing on Msbi online training
    Msbi online training Hyderabad
    Msbi online training India
    Msbi online course
    Msbi course
    Msbi training
    Msbi certification training

    ReplyDelete
  33. Outstanding I like this!!!!!!!!!! Hello guys, I want to tell you that this is an awesome blog and which you have written in this blog, each and every word is also great. Nice dear!! Thanks to sharing with us! Thanks a lot, dear!!!!!!!!!
    India VPS Hosing

    ReplyDelete
  34. I finally found great post here.I will get back here. I just added your blog to my bookmark sites. thanks.Quality posts is the crucial to invite the visitors to visit the web page, that's what this web page is providing.
    artificial intelligence course in bangalore

    ReplyDelete
  35. These mysql queries really helped us in our project. Thanks for this nice blog.
    Thanks
    Mereint Infotech
    https://merientinfotech.com/

    ReplyDelete
  36. The blog is quite resourceful, however a touch of the professionals can diversity it even further. Visit idealassignmenthelp.com and watch how the experts promise a fine quality my assignment help. The best experts in the business are here to take care of your academic needs.

    ReplyDelete
  37. Thanks for posting, the blog is very informative. If you want assignment help from top assignment writers, then idealassignmenthelp.com is for you. Choose your best assignment help writing service for instant assignment help.

    ReplyDelete
  38. This blog seems very informative and really a good post you writes! Now Get Assignment Help Australia from our great assignment writers. IdealAssignmentHelp.Com offers assignment help and assignment writing services for foreign students.

    ReplyDelete
  39. I feel SSIS is a very important component which actually helps in solving of many complex IT problems and facts.

    SSIS Upsert

    ReplyDelete
  40. The author of the blog certainly has extremely impressive writing skills. You can strengthen the blog even further by adding a few grammatical detailing that promises to take your written piece to another level. Explore the academic services offered by livewebtutors.com and seek assignment help. Read More Here….My Assignment Help | Assignment Help Australia

    ReplyDelete