LIKE in SQL – Is there a combination of “LIKE” and “IN” in SQL?

LIKE in SQL is used to compare and match a sequence of characters in a string (or messege). In this SQL tutorial, you’ll learn- What is LIKE in SQL? How to use LIKE? and Is there a combination of “LIKE” and “IN” in SQL? So, Let’s get started –

What is LIKE in SQL?


What is LIKE in SQL?

First of all, LIKE is an operator in SQL and not a Function. Most of the SQL beginners think it as a Function in SQL to SELECT DISTINCT values, but it’s completely wrong.

Technically speaking, The SQL LIKE operator is used to compare a value to find other similar values using something known as “wildcard operators”. The SQL engine provides 2 wildcards and all the Databases (like MySQL, Oracle, MS SQL Server, etc) support both of the wildcards perfectly.

Two wildcards used to find text in conjunction with the LIKE operator are-

1) The percent sign (%)
2) The underscore (_)

In simple words, the wildcards percent sign % and underscore sign _ help to create a pattern or formula (that we’ll see in detail below) to find matching text in table records and cells.

Let’s understand How do you use wildcards in SQL –


SQL LIKE wildcard

You can use LIKE operator to SELECT DISTINCT records or to UPDATE/INSERT MULTIPLE rows in a Table. Below is a table that explains both of the SQL LIKE wildcards –

The Percentage Sign % The Underscore Sign _
% wildcard is used to match one or more characters. _ wildcard is used to match only one character.
Remember***

Use asterisk * character instead of percentage sign %

if you are working with MS Access database.

Remember***

Use question mark ? character instead of underscore sign _
if you are working with MS Access database.

Syntax of LIKE in SQL

A generic syntax of SQL LIKE is :

SELECT  your_column1,
                 your_column2,
                   ...
FROM your_table_name
WHERE your_columnn_x LIKE your_pattern;

Some key notes about your_pattern used in the above syntax –

Pattern :

To find certain text you need to create a Pattern. The pattern is a sequence of alphabets/characters/numbers to search in the column cells of a table. These are some valid wildcard characters that you can use with the LIKE query :

1) The percent wildcard symbol (%): Finds any string having zero or more characters.

2) The underscore (_) wildcard: Finds any single character.

3) The [list of characters] wildcard: Finds any single character within the specified set.

4) The [characterA-characterB]: Finds any single character within the specified range.

5) The [^]: Finds any single character NOT within a list or a range.

These wildcard characters empower the SQL LIKE operator to do a better string comparison and display desired results.


Examples of LIKE

So far, we have learned LIKE keyword helps to find any text matching with any other number of texts. Now, we’ll learn by practicing examples- How LIKE operator works in SQL?

In order to work with LIKE command, we create a formula (or in simple words- a text using SQL LIKE wildcard) with percent symbol % and/or underscore symbol _ in combination with LIKE.

Let’s create a sample Database and Table, Insert some records and practically use the LIKE command on multiple records to understand the whole concept better. Here’re the 3 Simple steps –

A) Create a Database & Table for practice,

B) Insert some records,

C) Prepare and Run the SQL LIKE Query,

Don’t forget to verify the ResultSet that you’ll get, see below in detail.

Btw, Steps A and B can be optional if you already have some database and table for practice purposes. I’ve just listed them here for saving some of your time in doing Database setup and all other things.

To set up the Database schema, you can just download and run this setup-db.sql file from the Google drive or Copy/Paste the below SQL Queries directly in your SQL terminal


A) Creating DB and Tables

CREATE DATABASE db_tbl;

USE db_tbl;

CREATE TABLE tbl_movies (

    movie_id INT PRIMARY KEY AUTO_INCREMENT,
    movie_name VARCHAR (255) NOT NULL,
    price NUMERIC (5, 2) DEFAULT 0,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL

); ALTER TABLE tbl_movies AUTO_INCREMENT = 101;

 

B) Inserting some Records

INSERT INTO tbl_movies (
    movie_name,
    price,
    start_date,
    end_date
)
VALUES
    (   -- First record
        'Steve Jobs: 14 Years in Apple',
        25.50,
        '20200422',
        '20201126'
    ),
    (   -- Second record
        'ShubhamKLogic.com: A Tech Biography',
        30.00,
        '20210101',
        '20310101'
    ),
    (   -- And a few more...
        'Dangal',
        28.50,
        '20180201',
        '20181201'
    ),
    (
        'Avengers: End of Game',
        28.00,
        '20190123',
        '20190422'
    ),
    (
        'Friends : Forever',
        28.50,
        '20190123',
        '20190422'
    ),
    (
        'The Family Man (Year 2021)',
        28.00,
        '20190123',
        '20190422'
    ),

    (
        'Life_of_Pie',
        28.50,
        '20160901',
        '20170101'
);

 

C) Preparing SQL LIKE Query

So far with above SQL queries, we have successfully set up a Database and Table having 7 records in it. Here’re the records currently present in the table tbl_movies

SQL Table Records

Let’s have a look at different scenarios to solve different problems using LIKE in SQL Query –

Example 1) Find all the Movie details whose movie_name starts with ‘S’ :

Here’s a possible solution using LIKE with % wildcard:

SELECT *
FROM   tbl_movies
WHERE  movie_name LIKE 's%';

 

Output :

Output 2 of SQL LIKE QUERY


Example 2) Find all the Movie details whose movie_name contains digits of the 21st Century.

It simply means, find all rows in which the movie_name column must have at least one of the years between 2000 to 2100. Here’s a possible solution using LIKE with underscore _ and percent % wildcards :

SELECT *
FROM   tbl_movies
WHERE  movie_name LIKE '%2___%';

 

Output : When you’ll run the above query, you’ll successfully get the following results –

Output of above LIKE query returns 1 record

However, The above query will also fetch all the records having text in movie_name like: 2k21, but we’ll see how to filter them later.


Example 3) Find all movie details whose movie_name contains a semicolon : character.

Here is a possible solution using SQL LIKE command with % wildcard :

SELECT *
FROM   tbl_movies
WHERE  movie_name LIKE '%:%';

 

Output: When you’ll run the above LIKE query, you’ll successfully get the following results –

Output of SQL LIKE Query returns 4 records in this case.


Example 4) Find all movies details whose movie_name contains 2 underscore _ characters having some text in between them.

Here’s a possible solution using SQL LIKE wildcard and ESCAPE commands –

SELECT *
FROM   tbl_movies
WHERE  movie_name LIKE '%#_%#_%' ESCAPE '#';

 

Output : When you’ll run the above LIKE query, you’ll successfully get the following results –

The output of above SQL LIKE query returns 1 record.

 

Explaining : How to use LIKE in SQL in such cases? See here –

Learn- How to Use LIKE in SQL - ShubhamKLogic.com

From the above LIKE operator query explanation, here are some important key points –

  • In above query, The percent % wildcard character denotes that any text can be there.
  • The Hash # character denotes that the character after it is a literal character, so don’t treat it a wildcard or special character.
  • The Underscore _ character after # character will be treated as any other regular character as ‘a’ or ‘b’.
  • We used the ESCAPE keyword to define an escape character for custom use.

Example 5) Find all movie details having parenthesis () in movie_name.

Here’s a possible solution using LIKE in SQL –

SELECT *
FROM   tbl_movies
WHERE  movie_name LIKE '%(%)%';

 

Output : When you’ll run the above LIKE query, you’ll get the following result –

Output of above LIKE query returns 1 record


Is there a combination of “LIKE” and “IN” in SQL?

Take 3 points explained below to understand it better –

1) The IN operator allows you to check if the given value is getting matched with any value inside a set of values or returned by a subquery.

2) If you want to use IN operator, then you combine it with the NOT operator to determine if a value does not match any value in a list or a subquery. Another important use of IN operator is with WHERE clause of CRUD statements such as UPDATE, and DELETE.

3) While, the LIKE operator in SQL, is a Logical operator that checks whether a string contains a specified pattern or not.


What We Learned? 

So far we’ve learned, What is LIKE in SQL, and How to use it. Hope you found it helpful.

Don’t forget to check other Popular related SQL Articles that you may like:

Leave a Comment