How to Write a JQL Query for a String repeating in a Paragraph Field

Question

My goal is to filter issues based on whether a paragraph-type field contains two or more instances of "CS."  I want only issues where the field looks like: "CSxxxxxxx<cr>CSxxxxxxx"

I have tried several iterations, but the one I really expected to work is:
Project = XXXXX and CaseNumber ~ "CS*CS*"

Unfortunately this also returns issues where case number = "CSxxxxxxx"

Any thoughts?

Hannes Obweger - JXL for Jira · Answer

Hi @Christopher Hogan,

as already suggested, this isn't possible in plain Jira, however if you're open to solutions from the Atlassian Marketplace, you'll find options there.

E.g., this would be easy to do 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 you are after:

The regex that I'm using is

CS.*CS

which matches any description where CS occurs twice - depending on your other descriptions you may want to use a slightly more restrictive regex.

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

Ignacio Aredez GenAI Expert · Answer

JQL doesn't support direct queries for counting specific substring occurrences within a field. Your query Project = XXXXX and CaseNumber ~ "CS*CS*" doesn't differentiate between single and multiple occurrences of "CS" because JQL lacks the functionality to count specific substrings.

Workarounds:

Use ScriptRunner or a Similar Plugin: Create a scripted field that counts occurrences of "CS" in your target field and query against this new field.
Manual or External Analysis: Export issue data and use spreadsheet functions or a custom script to count occurrences outside JIRA.
JQL alone can't fulfill your requirement due to its limitations in pattern matching and counting substrings.