In SQL, the LIKE operator searches for character strings with the specified pattern using wildcards in columns. In SQL, pattern means a specific string of characters with wild cards to search for matching expressions.
Generally, we will use this
LIKE operator in the
WHERE clause. Following is the syntax of a like operator in the SQL server.
Note: You will Learn more about SQL wildcards in the next chapter.
We will check SQL like operator with examples for that first create “EmployeeDetails” table using the following script.
Once we execute the above SQL script, the table “EmployeeDetails” will create, and the following is the result.
Now run the following examples to check the LIKE operator in the SQL server.
The following SQL like query will return all employees whose location starts with character 'c' and followed by any string of characters because we mentioned a pattern like
'c%'. Here, the
'%' is a wildcard character that we will use before or after characters to search for a required matched string of characters.
Once we execute the above SQL query, our SQL like operator example result will be like as shown below.
The following SQL query will return all employees whose location ends with character
'r' because we mentioned a pattern like
'%r', which means it will return all the records whose location ends with a character
When we execute the above SQL like query, we will get the below result.
The following SQL query will return all the employees whose location contains a word called
'en' anywhere in the location column because we mentioned a pattern like
'%en%'. This means it will check for the respective word anywhere in the column irrespective of characters in front or back.
When we execute the above SQL like query, we will get the result below.
Now we will see how to use the NOT keyword with a LIKE operator and check how it will return records. Generally, using the NOT keyword with the LIKE operator will return the records that do not contain a matching pattern.
The following SQL statement will return all the employees whose location does not contain a word called 'en', anywhere within the location column because we used a NOT keyword with LIKE operator and mentioned a pattern like
When we execute the above SQL like operator with not keyword example query, we will get the result below.