Get a LIKE and build more flexible SQL queries
SELECT, think about normalizing and categorizing data. In
WHERE - about filtering with just a part of the value (for example email domain in
EmailAddress field) or with a single element of multi picklist (for instance finding one value within the comma-separated list of all purchased products).
There are two levels of
LIKE usage. First is focused on two essential character wildcards and is very straightforward — the second use full potential of group operator. Let's go through both with practical examples.
LIKE with Wildcard
Wildcards available in
|Zero, one or more characters|
|Exactly one character|
% symbol allows you to match zero, one or more characters within your
For example, you can search for
%@mateuszdabrowski.pl domain in
EmailAddress to get all contacts with an email address ending with
You can change this filter to
%@mateuszdabrowski.%. With the second wildcard at the end, it will be looking for any email address that contains
@mateuszdabrowski. but may have more text on either side of it. It will capture all emails ending with
@mateuszdabrowski.pl domain just as the previous filter, but also match those with
@mateuszdabrowski.sfmc.eu and other top-level domains.
In the above example, I used two wildcards - one before and one just after
@ symbol. It allows not only to catch contacts in
@mateuszdabrowski.pl domain, but also any subdomain, like
In the marketing automation world, however,
LIKE operator is especially useful for selecting contacts based on the multi picklist columns. Consider
ActiveSubscriptions field that can have any combination of values
Suppose you would like to select all contacts with active phone subscription. In that case, you shouldn't use equality operator (
WHERE ActiveSubscription = 'Phone'), as it would return only contacts that have just this one subscription. Some of your customers might, however, have multiple (phone and tv or phone and internet plans). For SQL, a field containing
'Phone' is not equal with the one having a value of
LIKE operator is here to save the day.
You Should Know
Always be sure to check whether you apply the wildcards correctly. Popular error is to have either too narrow or too wide scope of your filter. A common issue is lack of
% operator in the end or beginning of the searched string. For your query, it means that the value should end there.
WHERE EmailAddress LIKE '@gmail.com' should provide you zero results, as it would be looking for precisely the
@gmail.com value in the
EmailAddress field - without any characters before the
Another wildcard working with
LIKE is the underscore
_. It is much simpler as all it does it pretending to be any single character.
Let's consider a scenario, where you have a multi picklist field containing comma-separated product IDs. For example, a customer with three recommended products might have a value
1,14,27. The system codes single digit values to subscriptions, and two-digit codes to various product categories. Let's use it in the query.
In the query above, we are filtering our database with
RecommendedProducts field. To add a contact to our segment, in four steps, we check if at least one condition is true:
- If the record has exactly one recommended product that has a single-digit code (subscription)
- If the record has multiple recommended products and the first one is a single-digit (subscription)
- If the record has multiple recommended products and one in the middle is a single-digit (subscription)
- If the record has multiple recommended products and the last one is a single-digit (subscription)
LIKE with Group
There is also a group operator
LIKE statements. It allows you to create even more complex conditions:
|Any single character from the list specified inside|
|Range operator for |
|Exclusion operator for |
The group operator
 allows you to provide a list of characters that you want to find a match.
Consider the previous example with a field containing multiple IDs of your subscription and standard products. The field stores subscriptions in the form of single-digit product IDs. First four IDs are related to Premium subscriptions. IDs from 5 to 9 are Standard subscriptions.
If we would like to output only the Premium subscriptions, we would have to create a separate filter for each ID.
With a group, we can optimize it — a lot.
Thanks to the group operator, instead of covering every single case, we can provide all values within the
. Short, clean and readable. And it can get even better.
You Should Know
Group wildcard (
) is also useful as a way to escape character in the
LIKE statement. For example, you might want to look for all values, including a per cent sign (
LIKE '10%' is not a good idea, as it would return you all values starting with
10 - as the per cent sign is a wildcard meaning zero, one or more characters. You can, however, obtain what you want by writing:
By wrapping the
% with the grouping sign
, we are telling SQL to look for exactly the per cent sign. Thanks to it
'[%]' search will look for
4% values only.
Same works with
We can simplify more with the use of range operator
-. Instead of writing each possible character within our group
 (it gets awful when we are looking for any letter - lower or uppercase), we can ask for a range:
As you can see, all we need to do, it to put range operator
- between the first and last value that we want to catch. For alphabetical searches where we want both lowercase and uppercase letters, you will need to write it like this:
If you want to search for a hyphen '-' within a grouping operator that uses a range, add it in the very beginning.
LIKE '[-0-9] will look for either
- or any digit.
You Should Know
You can mix and match groups, ranges and wildcards.
It is an excellent tool to create essential categorization solutions for uncontrolled data. For example, to clean up free text job title coming from web forms.
There is one more operator that is sometimes useful - the exclusion operator
^. Add it to the beginning of your group, and SQL will not look for any of the grouped characters. For example,
LIKE '[^0-9]' will look for any character that is not a digit.
This operator is especially useful for the use in
WHERE statement. Best example? SQL for finding contacts with Subscriber Key not being correct Salesforce ID:
This short and straightforward query will look into each SubscriberKey and check whether in there is any character other the letter or digit (case-insensitive) or whether the length is not equal to 18. All such records have incorrect value and are eligible for cleanup or investigation. Perfect for Automation with deletion SSJS script or Verification Activity to alert administrator about issues.
Finally, just as with other statements, you can add
NOT statement before
LIKE to search for not matching records: