SQL LIKE Operator


The SQL LIKE operator is used in a SQL WHERE clause to filter or extract records for a specified pattern using wildcards (character string matches a pattern) in a table column.

You can use SQL LIKE operator when only a fragment of a text value of a column is known to search.

The SQL LIKE operator supports only text based columns like "CHAR", "VARCHAR" or "TEXT" to match a pattern.

The SQL LIKE operator can be used with in any valid SELECT SQL statement.

You can also search these topics using sql like operator, how to use like in sql, sql server not like, oracle select like, sql server like in, sql string match like, sql like expression, sql server like query example, sql wildcard search, sql query to search for a string, sql search operators, sql search query examples, sql search wildcard.

SQL LIKE Syntax

The basic syntax is : 


SELECT column_name1, column_name2 FROM table_name 
WHERE column_name LIKE 'search pattern';

Sample Database Table - Books

BookId BookName BookPrice RelYear DomainName AuthorName
1 A Visual Introduction To Sql 70 2008 Administration Nirmala
2 SQL Server 2008 Transact-Sql 170 2010 Database Ramanathan
3 SQL Functions Reference 205 2015 Optimization Vinoth Kumar
4 Complete Guide To No-SQL 195 2010 Database Ranjani Mai

SQL LIKE Operator Example

The following SQL SELECT statement selects the all books with a BookName starting "Sql", in the "Books" table:


SELECT * FROM Books 
WHERE 
BookName LIKE 'Sql%';

The result of above query is:

BookId BookName BookPrice RelYear DomainName AuthorName
2 SQL Server 2008 Transact-Sql 170 2010 Database Ramanathan
3 SQL Functions Reference 205 2015 Optimization Vinoth Kumar

The following SQL SELECT statement selects the all books with a BookName ending "Sql", in the "Books" table:


SELECT * FROM Books 
WHERE 
BookName LIKE '%Sql';

The result of above query is:

BookId BookName BookPrice RelYear DomainName AuthorName
1 A Visual Introduction To Sql 70 2008 Administration Nirmala
2 SQL Server 2008 Transact-Sql 170 2010 Database Ramanathan
4 Complete Guide To No-SQL 195 2010 Database Ranjani Mai

SQL LIKE Operator Example With Multiple Columns

The following SQL SELECT statement selects the all books with a BookName starting "Sql" and Relyear ending "0", in the "Books" table:


SELECT * FROM Books 
WHERE 
BookName LIKE 'Sql%' AND RelYear LIKE '%0';

The result of above query is:

BookId BookName BookPrice RelYear DomainName AuthorName
2 SQL Server 2008 Transact-Sql 170 2010 Database Ramanathan

SQL NOT LIKE Operator Example

The SQL LIKE condition can use with the SQL NOT operator.

The following SQL SELECT statement selects the all books with a BookPrice not starting "1", in the "Books" table.


SELECT * FROM Books 
WHERE 
BookPrice NOT LIKE '1%';

The result of above query is:

BookId BookName BookPrice RelYear DomainName AuthorName
1 A Visual Introduction To Sql 70 2008 Administration Nirmala
3 SQL Functions Reference 205 2015 Optimization Vinoth Kumar
You can also search these topics using wildcard search sql, sql keyword search, sql string contains, sql contains string, sql query contains, sql partial string match, sql regex replace, oracle regexp match, oracle pattern matching, sql pattern matching, sql server like command.