Building Search Feature Using the SQL LIKE Operator
If you’ve worked on an app or website for a long enough time then you’ve surely run into the issue of having more items to look through than is manually possible for users. While the widely accepted solution to this issue is to slap Elasticsearch on your project, there are other ways to allow users to find what they’re looking for.
For projects built with SQL, there is a statement structure and special operators that allow you to easily implement a lightweight search. With this you can avoid bloating your project with unnecessary dependencies while still offering a great user experience.
The Power of Like
The most basic SQL queries see you “SELECT” certain columns “FROM” a table and return the results. While this is great if you want to return specific columns, a lot of power comes from adding the “WHERE” keyword. This keyword allows you to specify conditions for certain columns in your query.
You can use “WHERE” to check for purchases that are over a certain amount, or for shirts made of a specific material. A basic use of “WHERE” allows for querying on concrete terms, but what if we don’t know exactly what we are searching for? This is where we turn to “LIKE.”
The “LIKE” keyword allows us to make less concrete queries. Say you have a house that you are wanting to tour and you only remember that it is on “Rosewood St.” You can’t find the result from a simple query because you will not get results if the first address line is something like “123 W. Rosewood St.” If you want to perform queries like this that you use “LIKE.”
“LIKE” looks for records that are similar, but not always the exact same. It uses query specific patterns to widen the records that meet the query. There are two operators that can be used to build these “LIKE” patterns.
The first operator is “_” which is a single character match. Say you have to get all your clients with the name “Lindsay,” but remember that some clients spell their name with an “e”. Using the “_” operator you can write the following query:
SELECT first_name, last_name, email FROM clients WHERE first_name LIKE ‘Linds_y’
This query will return all of your clients who spell their name with an “a” or an “e.” To understand this operator all you need to know is that anytime you see a “_” in a pattern, this character can have any value and return a query match.
For the next operator we will use our “Rosewood St.” example from earlier. Using the underscore “_” operator is useful if we are only unsure of a single character. Queries that require longer matching should utilize the “%” operator. This operator allows for matching unlimited characters in a pattern.
If we want to find our address then we can write the following pattern and query:
SELECT first_line_address, city, state, zip_code FROM properties WHERE first_line_address LIKE ‘%Rosewood St.’
In this query we see the ‘%’ replace everything to the left of “Rosewood St.” This works because the ‘%’ operator is a multi character wildcard. It doesn’t matter what comes before “Rosewood St.” As long as “Rosewood St.” is the last part of the first_line_address field, you will return the record.
The final, and one of the most common, pattern for searching results is “%string%.” This pattern is very common, and more flexible than the examples you’ve seen before. If you were looking for students whose first names included “ann” you could use the following query:
SELECT first_name FROM students WHERE lower(first_name) LIKE “%ann%”
This query would return the results “Anne”, “LeAnn” and “Ann.” This pattern is extremely important and should be used in most situations where you want a broader return from your queries.
Conclusion
With SQL LIKE queries you can build a very functional search for your records without bloating your project with extra libraries or services. If your service is built like a YouTube or Etsy, you may want to look into other options, but if you’re only dealing with hundreds or even a few thousand results, SQL LIKE statements should more than provide your users with a great experience. Let me know in the comments below how you plan to use SQL search in your projects!
