EDUCBA

Home Data Science Data Science Tutorials SQL Tutorial SQL Like with Multiple Values

SQL Like with Multiple Values

Introduction to SQL Like with Multiple Values

The following article provides an outline for SQL Like with Multiple Values. Any related records can be logically evaluated using the SQL LIKE operator. The LIKE operator allows us to specify one or more parameters. This enables users to carry out operations like selecting, deleting, and modifying any columns or records that meet the predetermined criteria. Where clauses are frequently used with it to provide the conditions.

SQL Like with Multiple Values

Key Takeaways

  • Text string patterns are matched using the LIKE operator. It conforms to the grammar requirements of a Boolean expression.
  • The usage of wildcards gives LIKE its actual strength.
  • LIKE does not care about the case. We can easily filter through big databases and extract the precise data we need if we become adept with the LIKE operator.

Overview of SQL Like with Multiple Values

One of the logical operators in SQL is the LIKE operator. If a value matches a pattern, the LIKE operator returns true; else, it throws an error. The given pattern row that we provided in the where and like conditions is found using the SQL LIKE operator. There seem to be two different kinds of wildcards used in like operators in SQL. The operator is first employed to replace a character or characters from the provided string. The supporting characters must precisely match the characters at the moment of pattern recognition.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

The LIKE operator determines whether an expression matches the pattern in this syntax.

To create a pattern, the SQL standard includes the user’s two wildcard characters:

  • Underscore wildcard accepts a specific character.
  • While % percent wildcard matches zero, one, or more characters.

Let’s look at how the LIKE operator may be used to filter the data returned so we only get the records we want.

The LIKE operator has the following syntax:

Syntax:

SELECT col_1, col_2, ... column_n
FROM table_name
WHERE col_1 LIKE pattern ('field names')

In this article, we’ll examine the LIKE clause’s functionality and how numerous criteria can be specified using it.

LIKE “lla%” Matches strings that begin with that symbol.
LIKE ‘%le’ Matches a string whose last character is le.
LIKE “%ch%” Matches a string containing “ch”.
LIKE ‘En_’ Matches strings that begin with En and end with a single character, such as Envelope, Enjoy…
LIKE “_at” Matches a string that contains the letter at and has a single character before it, such as cat.
LIKE “%are_” Matches a string containing the word are and ending in a single character.
LIKE ‘_are%’ Matches a string that contains the character, has a single character at the beginning and has any number of characters at the end.

It is an excellent method of searching when a string of characters matches one of the predetermined patterns or when we are unsure of what the user is looking for, often known as a fuzzy search. The LIKE operator is more adaptable thanks to the accessible wildcard characters.

How to Use SQL Like with Multiple Values in Operator?

Multiple Values are achieved using Logical Operators.

Formal syntax is given by:

SELECT col1,  col2,  ..col N
WHERE (col_name LIKE 'pattern'
OR col_name LIKE 'pattern'
OR col_name LIKE 'pattern')
FROM Table;

(Or) USING IN

SELECT * FROM TABLE_NAME WHERE COL_NAME IN (MATCH_VAL1, MATCH_VAL2);

Use of Comparison Operator >= to Equalize the Multiple Values

The below Query fetches a fee where it is greater than 6000 from the table course. Here the records fetched that match the specific conditions are displayed.

Command:

select * from course where fees >=6000;

Output:

SQL Like with Multiple Values 1

SQL Like with Multiple Values in the Same Column:

When used with the OR operator, the LIKE operator can be used to pick rows from a collection of string patterns.

Syntax:

SELECT col1, col2, col3...colN
WHERE (column_name LIKE 'pattern'
OR column name LIKE 'pattern'
OR column name LIKE 'pattern')
FROM TABLE_NAME;

In a SQL query, many like statements are possible. For instance, numerous like statements must be used to get a list of course names starting with “Jo” and “Am,” as shown below.

Command:

SELECT col name from table name where course LIKE 'Jo%' OR course LIKE 'AM'

In the below example, we are going to use ‘IN ‘ for matching multiple values.

Command:

Select * from course where fees IN (10000,2000);

Output:

going to use ‘IN ‘

Example of SQL Like with Multiple Values

We’ll use a straightforward database with little data for this topic.

The complete query used to generate the data is displayed below:

First, we shall create a path to work with MySQL. In this article, I have used xampp to work with SQL.

C:\Users\Suryakala KRAV>cd/
C:\>cd xampp
C:\xampp>cd pp
C:\xampp>cd mysql/bin
C:\xampp\mysql\bin>mysql -h localhost -u root;

The first step in putting the structured tables in the database using the structured query language is to create a database.

The SQL syntax used to construct a database is as follows:

CREATE DATABASE DATABASE name;

A database used here is test.

SQL Like with Multiple Values 3

Creating a Table:

Table Creation:

create table course(cid INT NOT NULL PRIMARY KEY, course_name varchar(30) NOT NULL, fees INT, dept int NOT NULL, willing BOOL NOT NULL);

Inserting Values:

insert into course values(01,'BE',10000, 0, TRUE);
insert into course values(02,'ME',20000, 0, TRUE);
insert into course values(03,'BSc',2000, 0, FALSE);
insert into course values(04,'MSc',2500, 0, TRUE);
insert into course values(05,'MCA',5500, 0, TRUE);

And our complete table looks like this:

Select * from course;

Output:

SQL Like with Multiple Values 4

Values with Specific Pattern:

Here if we wish to fetch the records where the course_name begins with ‘M’ we can use the below Query.

Command:

Select * from course where course_name LIKE "M%";

As shown in the below output, the table contains only record of the course whose course_name starts with M.

Output:

Specific Pattern

To retrieve records when a particular character equals a particular value. For instance, we may execute the following code to retrieve the record where the second character is “S”.

Command:

Select * from course where course_name LIKE "_S%";

Output:

retrieve the record

Conclusion

We learned how to use MySQL’s LIKE operator for multiple values in this article. The secret is to utilize distinct LIKE clauses for each matched column. All SQL users must be familiar with it because they are a fundamental component of standard SQL and operate on all types of databases. Understanding LIKE operators can help to create more effective queries.

Recommended Articles

This is a guide to SQL Like with Multiple Values. Here we discuss the introduction, how to use SQL like with multiple values in operator & example. You can also go through our other suggested articles to learn more –

  1. SQL REGEX
  2. SQL Like Wildcard
  3. SQL Server Functions
  4. SQL Injection