EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Data Science Data Science Tutorials SQL Tutorial SQL LIKE Operator

SQL LIKE Operator

Updated May 9, 2023

SQL LIKE Operator

Introduction to SQL LIKE Operator

SQL LIKE Operator is used with where condition to filter the table data by using a pattern that we have defined in the query. We can use SQL-like operators with date, string, and number values, but SQL-recommended operators must be used with string values. The like operator is not case-sensitive in MySQL, MSSQL, and SQLite databases but case-sensitive in PostgreSQL and Oracle databases.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

What is SQL LIKE Operator?

  • An ISO and ANSI standard operator compares one column value with another. We can also compare the quoted string by using the like operator.
  • It supports the different kinds of quoted-string wildcard characters. In addition, it will allow us to search a text-based string of a specified pattern.
  • It is used with the where clause in select, update, and delete SQL statements. We can use any wildcard character to find the specified pattern data from the column we defined in the query.

SQL LIKE Operator Pattern

It uses the following characters of wildcards to specify the pattern of the like operator as follows.

1. Percentage (%)

This pattern will match one, zero, or multiple characters or numbers.

Below is the syntax of the Percentage like operator pattern as follows.

Syntax:

Select name_of_column from name_of_table where name_of_column like 'xxx%';
Select name_of_column from name_of_table where name_of_column like '%xxx%';
Select name_of_column from name_of_table where name_of_column like '%xxx';

Below is an example of a percentage pattern as follows.

Code:

Select stud_id from like_operator where stud_addr like '%i';
Select stud_id from like_operator where stud_addr like 'M%';
Select stud_id from like_operator where stud_addr like '%U%';

Output:

percentage pattern

SQL LIKE Operator 2

SQL LIKE Operator 3

2. Underscore (_)

This pattern will match one character or number.

Below is the syntax of the underscore-like operator pattern as follows.

Syntax:

Select name_of_column from name_of_table where name_of_column like 'xxx_';
Select name_of_column from name_of_table where name_of_column like '_xxx_';
Select name_of_column from name_of_table where name_of_column like '_xxx';

Below is an example of the underscore pattern as follows.

Code:

Select stud_id from like_operator where stud_addr like '__ne';
Select stud_id from like_operator where stud_addr like 'Mumb__';
Select stud_id from like_operator where stud_addr like '_un_';

Output:

will match one characters or numbers

SQL LIKE Operator 5

SQL LIKE Operator 6

3. []

This pattern will match one character. So, suppose we have a defined pattern like ‘P[e, n, u],’ this string will match the Pune string.

Below is the syntax of the [] pattern as follows.

Syntax:

Select name_of_column from name_of_table where name_of_column like 'P[e, n, u]';

Below is the example of [] pattern as follows.

Code:

Select stud_id from like_operator where stud_addr like 'P[n, e, u]';

Output:

SQL LIKE Operator 7

4. [^]

This pattern will match every single character except the specified range. For example, the ‘P[^u, e, n]’ this string matches everything which starts from p, but it will not match the string which contains u, e, and n letters.

Below is the syntax of the [] pattern as follows.

Syntax:

Select name_of_column from name_of_table where name_of_column like 'P[^e, n, u]';

Below is the example of [] pattern as follows.

Code:

Select stud_id from like_operator where stud_addr like 'P[^n, e, u]';

Output:

will match every single character except the specified range

SQL-LIKE Operator Tables

It is also used with two tables. We can join the two tables and use the like operator on this after joining. The table slightly differs from the normal-like operator we used on a single column. Tables define query the like operator on two different tables.

Below is the syntax as follows.

Syntax:

Select name_of_column1, name_of_column2, …, name_of_columnN from name_of_table1, name_of_table2 where table_name.column_name like '%' + table_name.column_name like '%'

In the above syntax, we have used two tables. Therefore, we match standard columns from both tables to retrieve records from both tables.

We can also use all the tables from the column to retrieve matching records from the specified column, which we apply to both tables.

Below is an example as follows.

Example:

Code:

select id, number from A, B where a.id like '%' + b.number + '%';

Output:

SQL LIKE Operator 9

SQL Script LIKE Operator

SQL script-like operator is used in SQL DB systems. The SQL script-like operator will extract and match the specified pattern data. The SQL script-like operator performs large queries on our data tables. We can simplify complex queries by using SQL script-like operators. We are using an SQL script-like operator to speed up our query performance. When wildcards are used, the results are significantly faster. SQL Wildcards have already been used with a script-like operator in SQL. SQL script-like operator is used in our database to operate wildcards. When searching for complex data, we use SQL wildcards.

Below is an example of an SQL script-like operator as follows.

Example:

Code:

DECLARE @RuleName NVARCHAR (MAX)= 'SQL script like operator';
IF @RuleName LIKE 'SQL script 'SQL%'
PRINT 'Input';
ELSE
PRINT 'Output';

Output:

SQL LIKE Operator 10

List

We are using different types of SQL-like operators. The wildcard operator is mainly used with SQL-like operators.

Wildcard operator plays a vital role in SQL-like operators.

Below is the list of wildcard SQL-like operators as follows:

  • Percentage (%)
  • Underscore (_)
  • []
  • [^]

All the above operators we are using with SQL-like operators at the time of using the same in our query. Suppose we have a defined pattern like ‘AB%’; this string will match the AB string and all the strings coming after the AB string. Suppose we have a defined pattern like ‘AB_,’ this string will match the AB string and one character after the AB string.

Conclusion

It supports different kinds of quoted-string wildcard characters. It will allow us to search text-based strings of a specified pattern. It is used with the where condition to filter the table data by using a pattern that we have defined in the query.

Recommended Articles

We hope that this EDUCBA information on “SQL LIKE Operator” benefited you. You can view EDUCBA’s recommended articles for more information.

  1. SQL ORDER BY DESC
  2. SQL EXECUTE
  3. PL/SQL NOT EQUAL
  4. SQL NOT IN
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Blog as Guest
Courses
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW