We have already discussed about the SQL LIKE operator, which is used to compare a value to similar values using the wildcard operators. Show
SQL supports two wildcard operators in conjunction with the LIKE operator which are explained in detail in the following table.
The percent sign represents zero, one or multiple characters. The underscore represents a single number or a character. These symbols can be used in combinations. SyntaxThe basic syntax of a '%' and a '_' operator is as follows. SELECT * FROM table_name WHERE column LIKE 'XXXX%' or SELECT * FROM table_name WHERE column LIKE '%XXXX%' or SELECT * FROM table_name WHERE column LIKE 'XXXX_' or SELECT * FROM table_name WHERE column LIKE '_XXXX' or SELECT * FROM table_name WHERE column LIKE '_XXXX_'You can combine N number of conditions using the AND or the OR operators. Here, XXXX could be any numeric or string value. ExampleThe following table has a number of examples showing the WHERE part having different LIKE clauses with '%' and '_' operators.
Let us take a real example, consider the CUSTOMERS table having the following records. +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+The following code block is an example, which would display all the records from the CUSTOMERS table where the SALARY starts with 200. SQL> SELECT * FROM CUSTOMERS WHERE SALARY LIKE '200%';This would produce the following result. +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+----------+-----+-----------+----------+Summary: in this tutorial, you will learn how to use the SQL Server LIKE to check whether a character string matches a specified pattern. SQL Server LIKE operator overviewThe SQL Server LIKE is a logical operator that determines if a character string matches a specified pattern. A pattern may include regular characters and wildcard characters. The LIKE operator is used in the WHERE clause of the SELECT, UPDATE, and DELETE statements to filter rows based on pattern matching. The following illustrates the syntax of the SQL Server LIKE operator: column | expression LIKE pattern [ESCAPE escape_character] Code language: SQL (Structured Query Language) (sql)PatternThe pattern is a sequence of characters to search for in the column or expression. It can include the following valid wildcard characters:
The wildcard characters makes the LIKE operator more flexible than the equal (=) and not equal (!=) string comparison operators. Escape characterThe escape character instructs the LIKE operator to treat the wildcard characters as the regular characters. The escape character has no default value and must be evaluated to only one character. The LIKE operator returns TRUE if the column or expression matches the specified pattern. To negate the result of the LIKE operator, you use the NOT operator as follows: column | expression NOT LIKE pattern [ESCAPE escape_character] Code language: SQL (Structured Query Language) (sql)SQL Server LIKE examplesSee the following customers table from the sample database: The % (percent) wildcard examplesThe following example finds the customers whose last name starts with the letter z: SELECT customer_id, first_name, last_name FROM sales.customers WHERE last_name LIKE 'z%' ORDER BY first_name; Code language: SQL (Structured Query Language) (sql)The following example returns the customers whose last name ends with the string er: SELECT customer_id, first_name, last_name FROM sales.customers WHERE last_name LIKE '%er' ORDER BY first_name; Code language: SQL (Structured Query Language) (sql)The following statement retrieves the customers whose last name starts with the letter t and ends with the letter s: SELECT customer_id, first_name, last_name FROM sales.customers WHERE last_name LIKE 't%s' ORDER BY first_name; Code language: SQL (Structured Query Language) (sql)The _ (underscore) wildcard exampleThe underscore represents a single character. For example, the following statement returns the customers where the second character is the letter u: SELECT customer_id, first_name, last_name FROM sales.customers WHERE last_name LIKE '_u%' ORDER BY first_name; Code language: SQL (Structured Query Language) (sql)The pattern _u%
The [list of characters] wildcard exampleThe square brackets with a list of characters e.g., [ABC] represents a single character that must be one of the characters specified in the list. For example, the following query returns the customers where the first character in the last name is Y or Z: SELECT customer_id, first_name, last_name FROM sales.customers WHERE last_name LIKE '[YZ]%' ORDER BY last_name; Code language: SQL (Structured Query Language) (sql)The [character-character] wildcard exampleThe square brackets with a character range e.g., [A-C] represent a single character that must be within a specified range. For example, the following query finds the customers where the first character in the last name is the letter in the range A through C: SELECT customer_id, first_name, last_name FROM sales.customers WHERE last_name LIKE '[A-C]%' ORDER BY first_name; Code language: SQL (Structured Query Language) (sql)The [^Character List or Range] wildcard exampleThe square brackets with a caret sign (^) followed by a range e.g., [^A-C] or character list e.g., [ABC] represent a single character that is not in the specified range or character list. For example, the following query returns the customers where the first character in the last name is not the letter in the range A through X: SELECT customer_id, first_name, last_name FROM sales.customers WHERE last_name LIKE '[^A-X]%' ORDER BY last_name; The NOT LIKE operator exampleThe following example uses the NOT LIKE operator to find customers where the first character in the first name is not the letter A: SELECT customer_id, first_name, last_name FROM sales.customers WHERE first_name NOT LIKE 'A%' ORDER BY first_name; Code language: SQL (Structured Query Language) (sql)SQL Server LIKE with ESCAPE exampleFirst, create a new table for the demonstration: CREATE TABLE sales.feedbacks ( feedback_id INT IDENTITY(1, 1) PRIMARY KEY, comment VARCHAR(255) NOT NULL ); Code language: SQL (Structured Query Language) (sql)Second, insert some rows into the sales.feedbacks table: INSERT INTO sales.feedbacks(comment) VALUES('Can you give me 30% discount?'), ('May I get me 30USD off?'), ('Is this having 20% discount today?'); Code language: SQL (Structured Query Language) (sql)Third, query data from the sales.feedbacks table: SELECT * FROM sales.feedbacks; Code language: SQL (Structured Query Language) (sql)If you want to search for 30% in the comment column, you may come up with a query like this: SELECT feedback_id, comment FROM sales.feedbacks WHERE comment LIKE '%30%'; Code language: SQL (Structured Query Language) (sql)The query returns the comments that contain 30% and 30USD, which is not what we expected. To solve this issue, you need to use the ESCAPE clause: SELECT feedback_id, comment FROM sales.feedbacks WHERE comment LIKE '%30!%%' ESCAPE '!'; Code language: SQL (Structured Query Language) (sql)In this query, the ESCAPE clause specified that the character ! is the escape character. It instructs the LIKE operator to treat the % character as a literal string instead of a wildcard. Note that without the ESCAPE clause, the query would return an empty result set. In this tutorial, you have learned how to use the SQL Server LIKE operator to check if a character string matches a specified pattern. |