The SQL Like Operator: Tutorial with Examples of Usage

Web Development, Internet
The SQL Like Operator: Tutorial with Examples of Usage
Page content

The SQL LIKE Operator: Explanation and Syntax

The SQL LIKE operator is used in those situations when only a part of the information is recalled by us and we don’t have a clue about what the rest of the information is. At some point or the other everyone would have come across such a situation. For instance, let us consider a person’s name. You have a lots of names in your contacts list but you want a particular name that ends with one. Had you recalled the first two or three letters, the task would have been easy. In cases like these the SQL LIKE operator is the solution. Thus it becomes easier to get all the names ending with one.

SQL LIKE Operator**- Syntax:**

The syntax for SQL LIKE statement is

SELECT * FROM table_name WHERE column_name LIKE ‘part_information’;

The part information either starts or ends with a % wildcard. The part infromation is can also be enclosed within two %% wildcards (%part_information%). More than two wildcards can be used.

Breaking the Syntax in Parts:

First select all the columns. In real world, databases don’t have lots of columns. They are optimised to increase their performance. So it is safe to go ahead with the * operator.

The FROM clause is used to select the table name from which the columns have to be selected.

Using the WHERE clause, the particular column is selected from which we get the full information.

The part information is enclosed in single quotes (’ ‘).

Terminate the statement with a semicolon (;)

Examples:

SELECT * FROM friends where last_name like ‘%one’;

This statement will show all results from the last_name column where the last name ends with a one.

SELECT * FROM friends where last_name like ‘%on%’;

This statement will show all results from the last_name column where the last name string has an o followed by an n.

SELECT * FROM friends where age like ‘2%’;

This statement will show all results from the age column where the age is from 20 to 29.

SQL LIKE Operator - Real World Examples:

To understand the LIKE operator, first let us create a database and a table in it; populate the table (fill the table with data) and then operate on it.

  1. Create a database with any database name. In this example, the database is named hub. Write the following code in any SQL console. I am using a MySQL console.

CREATE DATABASE hub;

  1. In order to create a table in that database, first we have to use the database. To use the database enter the following code.

USE hub;

  1. Now create a table called ‘friends’ with four fields namely id, first_name, last_name and age. Enter the following code to create the table.

CREATE TABLE `hub`.`friends` (`id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,`first_name` VARCHAR( 30 ) NOT NULL ,`last_name` VARCHAR( 30 ) NOT NULL ,`age` INT( 3 ) NOT NULL) ENGINE = MYISAM ;

  1. Populate the table using the INSERT INTO statement.

INSERT INTO `hub`.`friends` (`id`, `first_name`, `last_name`, `age`) VALUES (NULL, ‘harry’, ‘potter’, ‘20’), (NULL, ‘ron’, ‘weasley’, ‘20’), (NULL, ‘hermione’, ‘granger’, ‘20’), (NULL, ‘albus’, ‘dumbledore’, ‘70’), (NULL, ‘hermione’, ‘smartone’, ‘20’);

Now you have a database with a table that is ready to be operated upon using the LIKE operator.

Use the following statements in your MySQL console and see the results.

SELECT * FROM friends WHERE last_name LIKE ‘h%’;

SELECT * FROM friends WHERE last_name LIKE ‘%one’;

SELECT * FROM friends WHERE last_name LIKE ‘%on%’;

SELECT * FROM friends WHERE age LIKE ‘2%’;

SQL LIKE - Wildcards:

Different wildcards are used in different databases management systems.

MS SQL and MySQL (the one on which I have tried all the above examples) use the % wildcard whereas MS Access uses the * wildcard. So it might be helpful to go online and check which wildcard can be used for the database that you are using.

A ‘-’ wildcard is another wildcard that is used to represent a single character.