Using the LIKE operator and wildcards

List of functions

In DPQL  you can use the LIKE operator in the WHERE clause to check for certain patterns.

You would use it alongside wildcards:

%  represents any number of characters

_  represents one character

Examples

Tickets from a single email domain

An example of this you could use would be if you wanted to look at all tickets from users under a specific email domain.

The query below wouldn't work as the email address is incomplete:

SELECT Tickets.id, tickets.person.emails.email
FROM tickets
WHERE tickets.person.emails.email = 'deskpro.com'

However if rather than = we use Like and the % wildcard we can pull all emails that end in deskpro.com

SELECT Tickets.id, tickets.person.emails.email
FROM tickets
WHERE tickets.person.emails.email LIKE '%deskpro.com'

Tickets from similar email domains

Similarly if we wanted to pull all tickets submitted from Deskpro.com and Deskpro.co.uk we could use the following as the second % would bypass the characters specified after deskpro:

SELECT Tickets.id, tickets.person.emails.email
FROM tickets
WHERE tickets.person.emails.email LIKE '%deskpro%'

Wildcard Variations

You can play around with the way you use wildcards to return values in different scenarios.

Below are some wildcard variations that return support@deskpro.com :

WHERE LIKE Description
WHERE  person.emails.email LIKE 'Support%' Any values that begin with support
WHERE  person.emails.email LIKE '%Deskpro.com' Any values  that end with deskpro.com
WHERE  person.emails.email LIKE '%Deskpro%' Any values that contain Deskpro
WHERE  person.emails.email LIKE 's%m' Any value that starts with S and ends with M
WHERE  person.emails.email LIKE '_u%' Any value that has a U at the second position

Published: 25/06/2019

Last updated: 26/07/2019