Like operator compares given character string with specified pattern. Pattern can include regular characters or wildcard characters. Below are the possible wildcard characters to make pattern.
Thank you for reading this post, don't forget to subscribe!| Wildcard character | Description | Example |
| % | Compares 0 or more characters in a string. | WHERE ProductName LIKE ‘%chai%’ displays all products where productname includes word ‘chai’. |
| _ (underscore) | Compares any single character in a string. | WHERE ProductName LIKE ‘_hai’ finds all four-letter first names that end with hai. |
| [ ] | Compares any single character within the specified range or set of characters like range [a-c] or set [abc]. | WHERE ProductName LIKE ‘[a-c]hai’ displays product name ending with hai and first character between a and c. |
| [^] | Compares any single character not within the specified range [^a-c] or set [^abc]. | WHERE ProductName LIKE ‘[^a-c]%’ displays all product name not starting with character range a,b and c. |
Syntax of LIKE Operator :
match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]
match_expression is valid expression of character string data type.
Pattern is the pattern to search for in match_expression,
escape_character is valid SQL Server expression of any of the data types of the character string data type category. escape_character has no default and must consist of only one character.
Return type of like operator is Boolean. LIKE returns TRUE If the match_expression matches the specified pattern.
Examples of LIKE Operator:
Example 1 : Using like operator in where clause
SELECT ContactName, CompanyName
FROM Customers
WHERE ContactName LIKE ‘abdul khursheed’
Output
ContactName CompanyName
Abdul Khursheed TCS
Above query compares table rows with pattern ‘abdul khursheed’ and returns rows having same value in contact name column.
Example 2 : Using % wildcard character in like operator
SELECT ContactName, CompanyName
FROM Customers
WHERE ContactName LIKE ‘abdul%’
Output
ContactName CompanyName
Abdul Khursheed TCS
Abdul Kadir TCS
LIKE ‘abdul%’ pattern returns all contact name having column value abdul followed by zero or more characters. You can also use NOT operator to find rows that doe not match with pattern. For example NOT LIKE ‘abdul%’.
Example 3 : Using [] square brackets wildcard charaters in like operator
SELECT ContactName, CompanyName
FROM Customers
WHERE ContactName LIKE ‘bd[ul]%’
Output
ContactName CompanyName
Abdul Khursheed TCS
Abdul Kadir TCS
Example 4 : Using wildcard charaters as literals in like operator
SELECT ContactName, CompanyName
FROM Customers
WHERE ContactName LIKE ‘ab[%]’
Above example serches for rows having column value ‘ab%’, here % sign is taken as charater to search from rows of table.
