ICON

Help - SQL LIKE operators

Help Center Data SQL LIKE operators

LIKE operators

2 MIN EXPERT DATA, SQL QUERIES, LIKE OPERATOR


This functionality is only available in Spotzi Studio.

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

Related blog posts

Interested in our solutions? Please contact our sales team.

Whether you are new to data analytics or already an expert. We offer solutions for everyone. You don’t need to be a data expert to work with Spotzi Location. For more tailor made solutions we offer Spotzi Studio. With this plan you cannot only manage the data yourself but also connect to our API and manage users for the dashboards you create. Or work with our Market Ready Dashboards and drive on autopilot.