The SQL provides us with the option to retrieve a specific set of data from the table, based on matching it with some sort of string with which we are unsure how it is stored in the database, e.g. if one does not know what is the actual spelling of certain thing, or he simply wants to search the database on the given values, the LIKE operator can be used in building a simple search bar on the website, which will get all the results from the database that matched the string given in the search Bar.
The LIKE operator is very helpful but it does take a time to fetch data, actually it is a bit slow on retrieving data as it compares all the values of the column with the mentioned value, the delay is not noticeable on small tables, but when it comes to huge tables containing million of records then the LIKE operator delay is noticeable. Now let’s see how we can write a LIKE operator.
SQL LIKE Syntax
Consider the following table
Firstname | Lastname | Age | Marital Status | Country |
---|---|---|---|---|
John | Smith | 40 | Married | Usa |
Mathew | Simon | 30 | Married | Uk |
Bill | Steve | 20 | Single | Usa |
Amanda | Rogers | 28 | Married | Germany |
Steve | Hills | 30 | Single | France |
Result of the Query
Firstname | Lastname |
---|---|
John | Smith |
Mathew | Simon |
Bill | Steve |
This Result of the query is including all the data set whose last name Start with "S"
Result of the Query
Firstname | Lastname |
---|---|
Bill | Steve |
Steve | Hills |
This Result of the query is including all the data set whose first name or last name contains the charcter set "ill"
Result of the Query
Firstname | Lastname |
---|---|
Bill | Steve |
This Result of the query is including all the data set whose Last name End with the Character "e"
The % Sign in the LIKE operator is a wildcard which you will learn better when you study the wildcard tutorial