MariaDB LIKE Operators


Objectives

  • Understanding of MariaDB LIKE Operator

  • MariaDB LIKE Operator Start with Where Condition

  • MariaDB LIKE Operator End with Where Condition

  • MariaDB LIKE Operator Contains Where Condition

  • MariaDB LIKE Operator Combination of Start & End with Where Condition

  • MariaDB LIKE Operator Positional Where Condition


Create database and table and insert data if it is not created already

--- Create database if not exist
CREATE DATABASE IF NOT EXISTS mariadb_tutorial;

--- Select the database for further operation
USE mariadb_tutorial;

--- Create table if not exist
CREATE TABLE IF NOT EXISTS person (
    id int(12) NOT NULL AUTO_INCREMENT,
    first_name varchar(150) NOT NULL,
    last_name varchar(150),
    email varchar(100),
    age int,
    income double,
    PRIMARY KEY (id)
)

--- Remove old Data
TRUNCATE person;

--- Insert data into table if not exist
INSERT INTO person (first_name, last_name, email, age, income)
VALUES
	('Faiyaz', 'Mia', 'faiyaz@pf.local', 1, 5000),
	('John', 'Doe', 'john@pf.local', 19, 100),
	('Tahsin', NULL, 'tahsin@pf.local', 10, 150),
	('Jane', 'Doe', 'jane@gmail.com', 26, 300),
	('Rakib', 'Mia', 'rakib@bf.local', 24, 200),
	('Sagor', 'Sowrov', 'email10@bf.local', 20, 250),
	('Touhid', NULL, 'hmtmcse.com@gmail.com', 30, 500);


Understanding of MariaDB LIKE Operator

LIKE Operator use for partial matching or specified pattern matching in WHERE condition. For example You want to know the list of person who are using gmail, so you don’t know the email prefix, but you know postfix of the email, xxxxx@gmail.com this types of problem you can solve by LIKE operator.

LIKE operator use 2 symbol or characters for wildcard match

  • % symbol represents zero, one, or multiple characters

  • _ symbol represents one, single character

Example

Condition Example Description

Starts with

LIKE 't%'

Finds any values that start with "t"

Ends with

LIKE '%d'

Finds any values that end with "d"

In any position

LIKE '%a%'

Finds any values that have "a" in any position

N position

LIKE '_a%'

Finds any values that have "a" in the second position

N and length

LIKE 'a_%_%'

Finds any values that start with "a" and are at least 3 characters in length

Starts with x and ends with

LIKE 't%d'

Finds any values that start with "t" and ends with "d"


MariaDB LIKE Operator Start with Where Condition

Problem: Find person whose name start with "t"

Syntax

SELECT * FROM table_name WHERE column_name LIKE "t%";

Example

SELECT * FROM person WHERE first_name LIKE "t%";

Output

+----+------------+-----------+-----------------------+------+--------+
| id | first_name | last_name | email                 | age  | income |
+----+------------+-----------+-----------------------+------+--------+
|  3 | Tahsin     | NULL      | tahsin@pf.local       |   10 |    150 |
|  7 | Touhid     | NULL      | hmtmcse.com@gmail.com |   30 |    500 |
+----+------------+-----------+-----------------------+------+--------+
2 rows in set (0.101 sec)


MariaDB LIKE Operator End with Where Condition

Problem: Find person whose email address end with "@bf.local"

Syntax

SELECT * FROM table_name WHERE column_name LIKE "%@bf.local";

Example

SELECT * FROM person WHERE email LIKE "%@bf.local";

Output

+----+------------+-----------+------------------+------+--------+
| id | first_name | last_name | email            | age  | income |
+----+------------+-----------+------------------+------+--------+
|  5 | Rakib      | Mia       | rakib@bf.local   |   24 |    200 |
|  6 | Sagor      | Sowrov    | email10@bf.local |   20 |    250 |
+----+------------+-----------+------------------+------+--------+
2 rows in set (0.001 sec)


MariaDB LIKE Operator Contains Where Condition

Problem: Find person whose firstname contain "n"

Syntax

SELECT * FROM table_name WHERE column_name LIKE "%n%";

Example

SELECT * FROM person WHERE first_name LIKE "%n%";

Output

+----+------------+-----------+-----------------+------+--------+
| id | first_name | last_name | email           | age  | income |
+----+------------+-----------+-----------------+------+--------+
|  2 | John       | Doe       | john@pf.local   |   19 |    100 |
|  3 | Tahsin     | NULL      | tahsin@pf.local |   10 |    150 |
|  4 | Jane       | Doe       | jane@gmail.com  |   26 |    300 |
+----+------------+-----------+-----------------+------+--------+
3 rows in set (0.000 sec)


MariaDB LIKE Operator Combination of Start & End with Where Condition

Problem: Find person whose email start with "j" and end with "l"

Syntax

SELECT * FROM table_name WHERE column_name LIKE "j%l";

Example

SELECT * FROM person WHERE email LIKE "j%l";

Output

+----+------------+-----------+---------------+------+--------+
| id | first_name | last_name | email         | age  | income |
+----+------------+-----------+---------------+------+--------+
|  2 | John       | Doe       | john@pf.local |   19 |    100 |
+----+------------+-----------+---------------+------+--------+
1 row in set (0.001 sec)


MariaDB LIKE Operator Positional Where Condition

Problem: Find person whose firstname second character "a"

Syntax

SELECT * FROM table_name WHERE column_name LIKE "_a%";

Example

SELECT * FROM person WHERE first_name LIKE "_a%";

Output

+----+------------+-----------+------------------+------+--------+
| id | first_name | last_name | email            | age  | income |
+----+------------+-----------+------------------+------+--------+
|  1 | Faiyaz     | Mia       | faiyaz@pf.local  |    1 |   5000 |
|  3 | Tahsin     | NULL      | tahsin@pf.local  |   10 |    150 |
|  4 | Jane       | Doe       | jane@gmail.com   |   26 |    300 |
|  5 | Rakib      | Mia       | rakib@bf.local   |   24 |    200 |
|  6 | Sagor      | Sowrov    | email10@bf.local |   20 |    250 |
+----+------------+-----------+------------------+------+--------+
5 rows in set (0.364 sec)


MariaDB LIKE Operators Video tutorial

Share