UXDE dot Net
Advertisement

MySQL Tutorial: Pattern Matching Using LIKE Clause

By -

Here is a tutorial on how to use LIKE keyword for pattern matching in MySQL select query.

MySQL Pattern Matching based on characters:

MySQL provides its own standard pattern matching based on characters. We can retrieve data according to a particular condition, that may involve some character matching by using LIKE in the WHERE clause instead of = or <>.

 Using Like Keyword:

You can retrieve the desired records from MySQL table having

  1. Starting from particular set of characters
  2. Containing particular set of characters
  3. Ending with particular set of characters
  4. Containing exactly of N numbers of characters

Understanding with examples:

Lets see every case with some intelligible examples before going any further with any explanation.

Syntax:

 SELECT *
 FROM table
 WHERE column_1
 LIKE character-pattern

 

Having the same table in the database

first_name last_name age date_registered
 1  Bob  Samuel  23  2012-12-15 09:56:42
 2  Anna  Smith  21  2012-12-16 10:26:02
 3  Dan  Brown  19  2012-12-17 11:54:22
 4  Agatha  Christie  17  2012-12-18 20:56:02
 5  John  Watson  30  2012-12-19 14:46:43
 6  Daniel  Patinson  21  2012-12-20 10:56:02

 

Records starting from particular set of characters:

Put a % sign at the very end of the set of characters to be started with.

Query:

SELECT *
FROM employee_data
WHERE first_name
LIKE ‘%da’

 Result:

mysql-like-clause-tutorial-example-1

Records Ending with particular set of characters:

Put a % sign right behind the the set of characters to be ended with.

Query:

SELECT *
FROM employee_data
WHERE first_name
LIKE ‘%y’

Result:

mysql-like-clause-tutorial-example-2

Records containing particular set of characters:

Put a % sign at the both ends of set of characters to be contained in the record and you will be able to extract all the records containing them.

Query:

SELECT *
FROM employee_data
WHERE first_name
LIKE ‘%o%’

Result:

mysql-like-clause-tutorial-example-3

Records containing exactly N numbers of characters:

This is as simple as useful and interesting it is. Put N number of underscores ‘_’ inside the single commas and extract the records having  exactly N number of characters

Query:

SELECT *
FROM employee_data
WHERE first_name
LIKE ‘_____’

Result:

mysql-like-clause-tutorial-example-4

Your comments will be acknowledged, if you got any question or want to share useful information.

Farheen Bibi

You can find Farheen on , and .

Leave a Reply