Search for issues that do not contain text

Dave Collins March 17, 2016

I need a simple solution for a JQL

eg: 

issuetype = Bug AND text != Apple

 

the != operator is not supported with text search. 

8 answers

1 accepted

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

16 votes
Answer accepted
Ignacio Pulgar
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 19, 2019

You can get all issues that do not contain the word 'Apple' by following these steps:

  1. Execute the following JQL: text ~ Apple
  2. Save your search as a filter, ie named "With Apple".
  3. Finally, execute this new JQL: filter not in ("With Apple")

After that, you'll get the list of issues that do not contain the word 'Apple'.

Ilze Schoeman February 21, 2020

Thank you so much for writing this comment it has helped me end my search for the answer!

Like # people like this
Halim Ansari July 29, 2021

Saving filter for each search keyword is not a practical solution, there has to be a better way.

Like Peter Krivoshik likes this
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 30, 2021

The real answer here is to put better data in - this is a clumsy search, and is nowhere near as flexible as you would like, but I would ask why you are trying to do searches like this?

The answer is probably "we didn't put the right data in the issues that would enable us to do sensible searches"

Like Danny Danhammer likes this
8 votes
Ignacio Pulgar
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 17, 2016

issuetype = Bug AND summary !~ "*On Production*"

Try with 'text' instead of summary, as I can't test now if it works as well.

Dave Collins March 17, 2016

Thanks, but no. Text does not accept != as an operator. 

Like # people like this
GabrielleJ
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 21, 2016

Dave, !~ is different from !=

Andrew Davison August 31, 2016

Thanks! !~ works perfectly.

Christopher Reece November 2, 2016

I cannot get the !~ to work with the text field. It works in summary, but does not appear as an option for text searches. If you force it, it doesn't work.

Like Peter Flynn likes this
Kane McConnell October 4, 2017

Why is this the accepted answer? 'text' and 'summary' are different fields in JIRA. The OP is asking about the 'text' field which does not support the !~ operator.

Like Peter Flynn likes this
Paul Smith December 15, 2017

Agree.  This answer is completely wrong and should be un-accepted.  You cannot use "!~" on text.

How can I negate a match on text?

Like Enric Martinez likes this
Peter Flynn January 4, 2018

This answer does not work for 'text'... should not be marked correct

Like Enric Martinez likes this
Ignacio Pulgar
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 9, 2018

Have just stumbled upon this old answer and felt the necessity to correct it, as suggested.

However, for consistency with the dates of previous comments, I'll post the correct answer within this comment instead of editing the original answer:

You can get all issues that do not contain the word 'Apple' by following these steps:

  1. Execute the following JQL: text ~ Apple
  2. Save your search as a filter, ie named "With Apple".
  3. Finally, execute this new JQL: filter not in ("With Apple")

After that, you'll get the list of issues that do not contain the word 'Apple'.

My original answer stated I didn't test it, so I was probably answerring posting from my mobile phone...

My main point was that '!~' should be used instead of '!=' with text fields.

As text function is sometimes used as a lazy way to avoid having to write longer JQLs which actually would target Summary and Description fields, some people might have found the correct syntax of DOESN'T CONTAIN '!~' a useful answer every now and then.

But, of course, the correct answer is the one I've just included into this comment.

Sorry for not having noticed this mistake before!

Best regards.

Like # people like this
Tyler Quillen July 21, 2018

Can confirm Ignacio Pulgar [Tecnofor]'s comment, this is an acceptable solution for searching "Does not contain this text". I do not know why, but it seems that the !~ operator does not work for text. 

 

As a note to this solution, you will likely have to include the majority of your search in the second query, see below example as an explanation.

You run a bakery and track your recipe creations in JIRA. The project is MyBakery, your epics are a custom issuetype called Recipe, and you want to find all of your recipes that do not contain Apples.

This will return all of your project specific recipes that include the word apples.

project = MyBakery AND issuetype = Recipe AND text ~ Apples

Save this as a filter, for this example, the filter name is 100.

 

In a new query, you can write...

filter != 100

This will return EVERYTHING in JIRA that is not in that filter. So to have it specific to your search you would rewrite the first half of your first search.

project = MyBakery AND issuetype = Recipe AND filter != 100

This will return all of the appropriate items that are not including the text Apples. 

 

Additionally, you can add multiple text options to mask out. If you had a customer who was allergic to Apples and Bananas, you could put in the first query 

project = MyBakery AND issuetype = Recipe AND text ~ "Apples OR Bananas"

 

Your JIRA version will dictate the correct syntax, see https://community.atlassian.com/t5/Answers-Developer-Questions/How-can-i-use-the-like-operator-with-the-List-of-values-in-Jira/qaq-p/545519 for reference.

Like # people like this
Stephanie Beach May 6, 2020

That was interesting. But I got it to work. For clarification.

I enter "QA PreTested" into the Test Notes for any bug that QA pre tested to ensure that the bug fails in QA or can be reproduced by QA.

I created  the filter Filter not in ("QA PreTested")

project in (ItemLogic, TestWiz, enCASE) AND type = bug AND status = "Ready for Development" AND updated >= endOfWeek(-2) AND "Testing Notes" ~ "QA PreTested" ORDER BY updated DESC

 

Then added that to the filter I wanted which now excludes any bugs I have pre-tested.

project in (ItemLogic, TestWiz, enCASE) AND type = bug AND status = "Ready for Development" AND updated >= endOfWeek(-2) and Filter not in ("QA PreTested") ORDER BY updated DESC

 

I'm surprised that ZQL has to make you go through this but anyway.

Stephanie Beach May 6, 2020

ps: I tried to post that as an answer but the submit button just depresses and nothing happens.

Enric Martinez June 26, 2020

"The operator '!~' is not supported by the 'text' field."

This is just silly.

Having to make a subquery just to negate a string? WTF?

WE chose Jira because it was supposed to be easier to retrieve information with JQL and handier to use than TRAC.  Just to find out the hard way that instead of a backtick for code you have to type half a sentence, that it doesn't work inline (also not the double curly braces explained somwhere) and that to make a simple negation of a string you need to write half a page of Javascript or hire a consultant while in TRAC you can just use plain SQL to search for anything you may wish for.

So, where does that leave us? A fancy GUI ?

1 vote
Ignacio Pulgar
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 21, 2020

Edited:

The NOT keyword doesn't work with text:

NOT text ~ apple

Neither this other equivalent syntax:

!text ~ apple

 

Peter Flynn March 16, 2020

For me, either of those syntaxes always makes the search just return zero results -- even though the non-negated version of the search shows that only about 100 of our 10,000+ issues contain the string I'm trying to include.

Ignacio Pulgar
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 17, 2020

Have just tried this query both in Cloud and Server 8.6.1 and the NOT and ! syntax do not work with text. Should have tried it before posting this answer.

Have just unaccepted this answer.

Please, refer to the new answer marked as accepted.

1 vote
Andrei Pisklenov _Actonic_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
June 10, 2019

You can use the "Power JQL" app - https://marketplace.atlassian.com/apps/1216891/power-jql-extended-search-functions?hosting=server&tab=overview
Which provides the ability to search by text fields using regex.
A few examples:
issue in powerIssue("project = SD", "summary,description", "^ABC-.*")
issue in powerIssue("project = SD", "text", ".*(bug|test).*") - for search by summary, description, comments.

1 vote
Tyler Quillen July 21, 2018

Can confirm Ignacio Pulgar [Tecnofor]'s comment, this is an acceptable solution for searching "Does not contain this text". I do not know why, but it seems that the !~ operator does not work for text. 

As a note to this solution, you will likely have to include the majority of your search in the second query, see below example as an explanation.

You run a bakery and track your recipe creations in JIRA. The project is MyBakery, your epics are a custom issuetype called Recipe, and you want to find all of your recipes that do not contain Apples.

This will return all of your project specific recipes that include the word apples.

project = MyBakery AND issuetype = Recipe AND text ~ Apples

Save this as a filter, for this example, the filter name is 100.

 

In a new query, you can write...

filter != 100

This will return EVERYTHING in JIRA that is not in that filter. So to have it specific to your search you would rewrite the first half of your first search.

project = MyBakery AND issuetype = Recipe AND filter != 100

This will return all of the appropriate items that are not including the text Apples. 

 

Additionally, you can add multiple text options to mask out. If you had a customer who was allergic to Apples and Bananas, you could put in the first query 

project = MyBakery AND issuetype = Recipe AND text ~ "Apples OR Bananas"

 

Your JIRA version will dictate the correct syntax, see https://community.atlassian.com/t5/Answers-Developer-Questions/How-can-i-use-the-like-operator-with-the-List-of-values-in-Jira/qaq-p/545519 for reference.

1 vote
Dave Collins March 17, 2016

?? Opposite of my need. Using ~ would result in what issues I do not want to see. 

If I have 100 cake recipes, of many various kinds, but hate Apples and don't want to see any of the recipes that include the use of Apple. 

examples:

~ Apple = show me recipes (issues) with Apple in them

!~ Apple = do not show me recipes (issues) with Apple in them

 

I want a JQL for the latter of these examples. 

0 votes
holzkohlengrill December 20, 2021

I think many attempts here are correct.

However for me there was an important detail missing (-> the wildcards):

 

issuetype = Bug AND text !~ Apple

did not work for me; however:

issuetype = Bug AND text !~ '*Apple*'

did work.

0 votes
Thanos Batagiannis _Adaptavist_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 17, 2016

Do you want to match the exact text ? If not then you can use the contains (~) and in your case can be does not contain (!~) 

Ratna Kumar April 3, 2018

what is the JQL i have to user if i need exact text? Using ~ symbol it returns all issues that has the particular search string.  But i need only those issues that are exactly match to the search string.

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

TAGS
AUG Leaders

Atlassian Community Events