how can i query all records in mysql where field is not empty or null?

Question

such as bellow, some products_name is empty ,and some is null , how to get other has values records? enter image description here

-- ----------------------------
-- Table structure for `a`
-- ----------------------------
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `products_id` int(11) NOT NULL,
  `products_name` varchar(255) default NULL,
  PRIMARY KEY  (`products_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of a
-- ----------------------------
INSERT INTO `a` VALUES ('1', 'hello');
INSERT INTO `a` VALUES ('2', '222');
INSERT INTO `a` VALUES ('3', null);
INSERT INTO `a` VALUES ('4', '');
INSERT INTO `a` VALUES ('5', '5555');

Sajuna Fernando · Accepted Answer · 2013-10-12 08:26:44Z

3

You could just do:

SELECT * FROM `a` WHERE `products_name` IS NOT NULL AND `products_name` != '';

answered Oct 12, 2013 at 8:26

Sajuna Fernando

1,38411 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Prahalad Gaggar Over a year ago

Try to use IFNULL

Sajuna Fernando Over a year ago

Why would you need IFNULL? He wants to get the records where the name is NOT NULL and not empty isnt it? In case he wants all records but wants to do something else with the NULL fields he could use IFNULL. Correct me if i'm wrong.

Prahalad Gaggar Over a year ago

how can i query all records in mysql where field is not empty or null?

Sajuna Fernando Over a year ago

Just change the AND Constraint in the query i posted above to an OR constraint.

Bohemian · Accepted Answer · 2013-10-12 08:53:19Z

3

You can express it succinctly as:

SELECT * FROM a
WHERE ifnull(products_name, '') != ''

answered Oct 12, 2013 at 8:53

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Collectives™ on Stack Overflow

how can i query all records in mysql where field is not empty or null?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related