SQL LIKE

SQL LIKE Operators

access_time 5 MIN  | fiber_manual_record EXPERT  | label DATA, SQL QUERIES, LIKE OPERATOR

If you want to use an SQL query that filters a value based on a specific pattern you can use the LIKE operator. LIKE operators can only be used on fields of type text (string). A LIKE query looks like this:

SELECT * FROM {name of dataset}
WHERE {column} LIKE {pattern};

The LIKE query may contain 2 kinds of wildcards:

  • % – This sign represents zero, one or multiple characters
  • _ – The underscore represents a single character

With those wildcards you can create the following WHERE queries:

LIKE Operator Description
WHERE {column} LIKE ‘a%’ Finds all values that starts with “a”
WHERE {column} LIKE ‘%a’ Finds all values that ends with “a”
WHERE {column} LIKE ‘%or%’ Finds all values that have “or” in any position
WHERE {column} LIKE ‘_r%’ Finds all values that have “r” in the second position
WHERE {column} LIKE ‘a_%_%’ Finds all values that start with “a” and areat least 3 characters in length
WHERE {column} LIKE ‘a%o’ Finds all values that start with “a” and end with “o”

A few examples

In the example below we have a dataset with 2-digit postal codes from the US. Each postal code area has one sales manager, but a sales manager can be responsible for several postal code areas.

LIKE Query

Example 1: Let’s say we only want to show the areas where the name of the responsible sales manager starts with a “J” (case-sensitive). In that case the WHERE query will look like this.

SELECT * FROM usa_salesmanagers_2_digit_areas
WHERE salesmanager LIKE 'J%';

LIKE Query

Example 2: Let’s say we only want to show the areas where the name of the responsible sales manager has a name that contains “am” (case-sensitive). In that case the WHERE query will look like this.

SELECT * FROM usa_salesmanagers_2_digit_areas
WHERE salesmanager LIKE '%am%';

LIKE Query

Example 3: Now let’s say we only want to show the areas where the name of the responsible sales manager has a name that contains a “c” as a third letter (case-sensitive). In that case the WHERE query will look like this.

SELECT * FROM usa_salesmanagers_2_digit_areas
WHERE salesmanager LIKE '__c%';

LIKE Query