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.
Related Links
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 |
Related Links