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

[Phil Bustin]

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

[Phil Bustin]

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.

[Hannes Obweger - JXL for Jira]

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 hierarchies, issue 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:

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]

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