TIL how to properly write a SQL statement using the
LIKE operator, including how to use wildcards to match various patterns.
I spent the day working on a Java service to pull data from our Business Intelligence database (MySql). The data in which I was interested involved a string column (name) and an integer column (id). I wanted to write a query that matched any name OR any id that started with a prefix that the user input to the service.
like operator allows you to write a sql query of the format “Give me results that contain a particular pattern.” You can customize the pattern to match on either prefixes, postfixes, substrings, or other more complicated patterns.
The query above will return rows where the name contains the substring ‘abc’. I believe mysql is converting the integer column to a string to perform the matching under the hood.
NOTE: I also learned that
varchar and other character string columns will be matched in a case insensitive manner, while
varbinary and other binary columns will match patterns in a case sensitive manner. Something to keep in mind when designing your tables!
There are two wildcards that can be used in your patterns
_matches on a single character
%matches on any number of characters (including 0)
You can use
% in the same pattern to perform complex matches. Here are a few more examples:
Solving my Problem
I needed to write a query that returned all rows where either the name or id column started with a prefix that the user passed. The statement that got me there was the following:
I made sure to add a distinct constraint to prevent duplicates from being returned, and ensuring sorted order by id. These were things that our service was doing in application code previously… doing these types of sorting and filtering operations in the database makes much more sense.
EDIT: Well we switched from
postgres, and I realized that the
like operator performs a case-sensitive search in postgres regardless of the column type! If you want case insensitivity in your searches in
postgres, use the
ilike operator instead.