SQL Server LIKE condition or operator is used to perform pattern matching. It is used with WHERE clause and SELECT, INSERT, UPDATE and DELETE statement.
expression LIKE pattern [ ESCAPE 'escape_character' ] Parameter Explanation
expression: It is a character expression like a column or field.
pattern: It is a character expression that contains pattern matching.
Following is a list of patterns used with LIKE operator:
| Wildcard | Explanation |
|---|---|
| % | It is used to match any string of any length (including zero length). |
| _ | It is used to match on a single character. |
| [ ] | It is used to match on any character in the [ ] brackets (for example, [abc] would match on a, b, or c characters) |
| [^] | It is used to match on any character not in the [^] brackets (for example, [^abc] would match on any character that is not a, b, or c characters) |
LIKE Operator using % wildcard (percent sign wildcard)
Example:
You have a table named “Students”, having the following data:
Let’s use % wildcard with SQL Server LIKE condition. Here we retrieve all of the students from “Student” table whose name begins with ‘L’.
SELECT *
FROM [javatpoint].[dbo].[Student]
WHERE name LIKE 'L%';Output:
SELECT *
FROM [javatpoint].[dbo].[Student]
WHERE name LIKE '%L%';
LIKE Operator using [ ] wildcard (square brackets wildcard)
Let’s use [] wildcard with SQL Server LIKE condition.
SELECT *
FROM [javatpoint].[dbo].[Student]
WHERE name LIKE 'Aj[ie]et%';Note:
It will return all students whose name is 5 characters long, where the first two characters is ‘Aj’ and the last two characters is ‘et’, and the third character is either ‘i’ or ‘e’. So in this case, it would match on either ‘Ajiet’ or ‘Ajeet’.
Output:
LIKE Condition with NOT Operator
Let’s retrieve all of the students from “Student” table whose name is not begin with ‘L’.
SELECT *
FROM [javatpoint].[dbo].[Student]
WHERE name NOT LIKE 'A%';Output:
Leave a Reply