Can I filter a custom short text field to exclude alphabetic characters, as well as a string?

Phil Bustin
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.
April 1, 2024

I haven't found a way to exclude alphabetic characters from a short text field, and I haven't found a way to exclude a specific 3-letter prefix that would always occur either as the first three characters, or if the user was careless, the first three characters after a space.

If I were able to find both filter components, I would need to find a way to combine them in one filter, or use one filter for one component, and reference the first filter in the second.  I saw the latter technique in a Community post.

The following Community post answer didn't work for me, nor did others.

https://community.atlassian.com/t5/Jira-Software-questions/JQL-Does-not-contain-operator-usage/qaq-p/906762#U2657717

 

2 answers

1 vote
Phil Bustin
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.
April 1, 2024

I've managed to put together exclusions for most of the letters of the alphabet, except for a few that are in strings I do not want to exclude.

     AND !("SBM Tracking No" ~ "T*")

However, 20 or so of the above, covering most of the alphabet, doesn't cover instances where a letter is the last character of the text, and contradicting the following document

     https://confluence.atlassian.com/jirasoftwareserver/search-syntax-for-text-fields-939938747.html#Searchsyntaxfortextfields-fuzzyFuzzysearches:~

the following creates an error, as shown:

     AND !("SBM Tracking No" ~ "*T")

The text query '*A' for field 'SBM Tracking No' is not valid: the '*' and '?' are not allowed as first character in wildcard query.

     

 

0 votes
Hannes Obweger - JXL for Jira
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
April 2, 2024

 

Hi @Phil Bustin

do I understand correctly that you want to list all issues where a particular field matches a certain text pattern? 

I would probably also start with JQL's wildcard parameter "*", but as you are showing above, it is somewhat limited. As an alternative, you may want to consider an option from the Atlassian Marketplace. There are a number of apps available that either extend JQL by additional functions, or offer their own, more powerful search and filter mechanisms.

E.g., if a solution from the Atlassian Marketplace is an option for you, I believe that your use case would be easy to solve using the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a number of advanced features, including support for (configurable) issue hierarchiesissue grouping by any issue field(s), sum-ups, or conditional formatting - as well as the ability to filter issues via regular expressions

With regular-expression-based filtering, you can apply pretty much any logic, including the one that I understand you are after:

regex-shipment-nr.gif

The regex that I'm using is

^(?![a-zA-Z]{3} {0,1}).+$

There's a few things going on here, but TLDR, it checks if the value does not start with either a 3-character string or a 3-character string followed by a space character. You may need to tweak the regex to match your exact use case (happy to help!).

Once you've identified your issues, you can work on them directly in JXL (e.g., bulk edit them via copy/paste), or trigger various operations in Jira.

Any questions just let me know,

Best,

Hannes

Phil Bustin
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.
April 2, 2024

Thank you, but I'm limited to no marketplace products for now.

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
PREMIUM
PERMISSIONS LEVEL
Site Admin
TAGS
AUG Leaders

Atlassian Community Events