How to select strings beginning with several letters in mariadb?-CodePudding

I want to exclude the strings in the address column which start with the letters C, G or S.

I have tried this expression,

SELECT * FROM guest_list

WHERE  address NOT LIKE  ("C%" OR "F%" OR "G%");

and it gives me the warnings like this:

'Warning: truncated incorrect DOUBLE value: 'C%'
'Warning: truncated incorrect DOUBLE value: 'G%'

here is the script to create my table.

CREATE TABLE `guest_list` (
  `id` int(11) NOT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

   
INSERT INTO `guest_list` VALUES (1001,'Jim','Dowd','Lewisham West and Penge'),
(1002,'Lyn','Brown','West Ham'),(1003,'Ann','Clwyd','Cynon Valley'),
(1004,'Nic','Dakin','Scunthorpe'),(1005,'Pat','Glass','North West Durham'),
(1006,'Kate','Hoey','Vauxhall'),(1007,'Mike','Kane','Wythenshawe and Sale East'),
(1008,'John','Mann','Bassetlaw'),(1009,'Joan','Ryan','Enfield North'),
(1010,'Cat','Smith','Lancaster and Fleetwood'),(1011,'Mark','Tami','Alyn and Deeside'),
(1012,'Keith','Vaz','Leicester East'),(1013,'Ian','Austin','Dudley North'),
(1014,'Liam','Byrne','Birmingham, Hodge Hill'),(1015,'Ann','Coffey','Stockport');

What is the correct way to do this with LIKE please (if one does not use regex)?

CodePudding user response：

LIKE is standard operator for comparing a column value to another column value, or to a quoted string.

"C%" OR "F%" OR "G%" is not a quoted string, but a mathematical operation with the result 0, since all values are string (but not numerical) values and converted to zero.

Instead of using one LIKE operator, you have to use n LIKE operators, where n is the number of your conditions:

SELECT columns FROM guest_list
WHERE address NOT LIKE ("C%") AND address NOT LIKE("F%") AND address NOT LIKE("G%")

By default, LIKE conditions are case-insensitive, so if you want to filter only uppercase, you have to specify a binary collation, e.g. LIKE ("C%" collate latin1_bin)..