MySQL LIKE operator

LIKE operator for filter the retrieve data and perform wildcard text searches.

• Don’t misplaced the wildcard symbols, if you do, you will not get the result as you want
• Wildcard search patterns are the slowest to process
• Use another search operator instead of wildcard, such as full-text search which is better in performance

Create Table and Insert Sample record for use in this tutorial

CREATE TABLE `todo` (

`t` datetime NOT NULL,

`content` varchar(255) NOT NULL,

`status` enum('open','closed','in-progress') NOT NULL,

`id` int(11) NOT NULL auto_increment,

PRIMARY KEY  (`id`),

KEY `t` (`t`)

) ;

INSERT INTO `todo` VALUES ('2010-02-20 14:11:32','what is your name','open',1),

('2010-02-20 14:10:30','go to work man','closed',2),

('2010-08-15 23:12:35','lovely','open',6),

('2010-08-15 23:12:59','lovely','open',7);

LIKE ‘%TEXT%’

SELECT * FROM todo;
 SELECT * FROM todo WHERE content LIKE '%your%';
 SELECT * FROM todo WHERE content LIKE 'go%';
 SELECT * FROM todo WHERE content LIKE '%man';

Description

% matches any number of any character or number. In the second query, it queries data that contains the work your. The third query, it queries data that start with the word go and follow by one or many character or number. The fourth query, it queries data that starts with one or many characters or number and ended with the word man

LIKE operator 2

SELECT * FROM todo WHERE content LIKE '_ovely';
 SELECT * FROM todo WHERE content LIKE 'w%k';
 SELECT * FROM todo WHERE content LIKE 'w%%k';
 SELECT * FROM todo WHERE content LIKE '%w%k';

SELECT * FROM todo WHERE content LIKE '%w%k%';




Display:



Description

First query, the underscore(_) uses for single character. It returns query that contains the word such as lovely, movely, oovely or movely etc. In this query, the word lovely is return
Second query, it returns query that starts with w, follows one or many characters or numbers, end with k
Third query, it returns query that starts with w, follows one or many characters or numbers, end withk
Fourth query, it returns query that starts with one or many characters or numbers, follow with w than follows by one or many characters or numbers, follow with k, and end with one or many characters or numbers