How do I query the status field for items that do not include the string "imp"?

[Dave Wiggins]

I need to filter out items with a status contains the string "Imp".

[John Funk]

Hi Dave - Welcome to the Atlassian Community!

First question is how many statuses do you have total that would have that string? And can you not just list them with AND Status not in (IMP1, IMP2, IMP3)

[Suraj Aderogba]

Hi @Dave Wiggins ,

Welcome to Atlassian community.

Good question, this is a common use case when you want to exclude specific words or partial matches in Jira JQL searches.

If you’re trying to filter out any issues whose Status contains the word “Imp” (for example: Implemented, In Progress (IMP), Improvement, etc.), you can use the !~ operator in your JQL.

JQL Query:    status !~ "Imp"

This means: Return all issues where the Status field does not contain the text ‘Imp’ (case-insensitive).

Hope that helps you clean up your results list!

Suraj

[Bill Sheboy]

Hi @Suraj Aderogba 

The CONTAINS ~ operator for JQL is not supported with the Status field.  Where were you able to make the query you suggested work in Jira?

Kind regards,
Bill

[Suraj Aderogba]

@Bill Sheboy you’re right JQL doesn’t support partial-match (~) on status field. Its a workarounds by copying the status name to a custom text field. That way, I can then run JQL with the ~ operator.

[Matteo Vecchiato]

Hi @Dave Wiggins ,

Welcome to Atlassian community.

It isn't possible to query the status containing a string, instead you can indicate the list of status.

The only workaround is to copy the status name to a custom text field and maintain it synched continously. This could be done with automation.

Having a custom text field, you can create a jql that search for a partial string.

I hope it helps.

Regards

[Iryna Komarnitska_SaaSJet_]

Hi @Dave Wiggins ,

Can you describe the use case in more detail? Why do you need this JQL? What is the ultimate goal?

[Martin Sturm - JXL]

Hi @Dave Wiggins 

If you're open to solutions from the Atlassian Marketplace, you may want to have a look at the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your Jira data that allows viewing, inline-editing, copy-pasting, sorting, and filtering by all your work items' fields, much like you’d do in e.g. Excel, Google Sheets, Smartsheet, or Airtable.

It also allows you to set up hierarchies and specific filters on hierarchy levels. In this screenshot for example I am filtering out all Work items where the Status contains the string "Deliver":

Once saved, you see the work items are gone:

I hope this helps.

Regards, Martin

[Gor Greyan]

Hi @Dave Wiggins

As @Matteo Vecchiatomentioned, there is no query for containgin string, when searching the statuses.

You can do it via API Call, Bulk getting all statuses, then searching among them your needed string.
/rest/api/3/statuses

You can check API's via the following link.

https://developer.atlassian.com/cloud/jira/platform/rest/v3/api-group-status/#api-rest-api-3-statuses-get