CODEWITHSUNDEEP
mysql
mingfish_004
mingfish_004

Reputation: 1413

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

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

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

Upvotes: 1

Views: 2833

Answers (2)

Bohemian
Bohemian

Reputation: 425358

You can express it succinctly as:

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

Upvotes: 3

Sajuna Fernando
Sajuna Fernando

Reputation: 1384

You could just do:

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

Upvotes: 3

Related Questions