JQL Like Query Operator in Jira

Jira Query Language (JQL) is a query language that’s quite similar to SQL. That being said, the syntax for doing something similar to SQL’s LIKE statement is nothing like how you’d do it in SQL.

Deviating from the syntax that inspired it isn’t uncommon, but it’s quite peculiar that Jira Query Language reserves the word ‘LIKE,’ even when not explicitly used for fuzzy matching.

Like syntax in JQL

To start, Jira actually makes it hard to find documentation on this. Instead of calling them “like” statements, similar to SQL, they refer to it as the “contains” operator. The “contains” operator is the tilde ~ and looks something like this:

field ~ searchtext
JQL

Furthermore, according to the documentation, the contains query operator allegedly attempts to match on derivatives. Searching for “file” will also match “files”, which in all honesty, I’m not a big fan of. I like my queries to be explicit and not come with any weird magic that I have to remember happens.

Searching with wild cards

Additionally, the contains operator also supports wild cards. Unlike SQL’s wild card characters, % and _, JQL uses an asterisk * instead:

field ~ "searchtext*"
field ~ "*searchtext"
JQL

Multiple word search caveats

Another bit of magic to keep track of is how Jira Query Language interprets multiple words as part of your search. If you are searching for a multiple word phrase, keep in mind that including a space in your search text implies “match BOTH of these words” and not “match the entirety of this phrase”:

field ~ "this that"
JQL

Surprisingly, this means it will search for “this” and “that”. Fortunately, there is a way to search for the entirety of a phrase. If you’re looking for “this that”, you’ll need to quote (and escape) the string like this:

field ~ ""this that""
JQL

Not Like syntax in JQL

Typically, query operators offer an inverse operation, which in this case allows querying for strings not contained in a particular search phrase. To accomplish this, simply slap a bang ! on the query operator:

field !~ searchtext
JQL
Josh Sherman - The Man, The Myth, The Avatar

About Josh

Husband. Father. Pug dad. Musician. Founder of Holiday API, Head of Engineering and Emoji Specialist at Mailshake, and author of the best damn Lorem Ipsum Library for PHP.


If you found this article helpful, please consider buying me a coffee.