How do you write a query for checking if a field only has a whitespace character?

USE CASE NEED:

  • To return a list of Defects that do not have an External ID (i.e. "External ID" is EMPTY)

PROBLEM:

  • External ID could have been accidentally given a whitespace character, and this should be treated as it still being EMPTY
    • So how do you write a JQL query that returns the list of defects if it is Empty or if it only has whitespace?

 

The initial JQL query, without solving the problem, is:

issuetype = Defect and "External ID" is EMPTY

I can't figure out how to have that as an OR where it checks if it's empty or if it just has whitespace characters

5 answers

1 accepted

1 vote
Accepted answer

You could get these results using ScriptRunner for JIRA's issueFieldExactMatch script JQL Function.

issuetype = Defect and ("External ID" is EMPTY OR issueFunction in issueFieldMatch("", "External ID", "^\\s*$"))

The above would find any issues where the contents of the field was only whitespace, or the field was empty. You could use more elaborate regular expressions to search for single-character entries and the like.

In fact, if you had a pretty good idea what the format of your External ID field should be, then you could write a query to find every issue where the External ID field did not match the expected format.

issuetype = Defect AND NOT (issueFunction in issueFieldMatch("", "External ID", "^external ID regex goes here$"))


You were so close to being spot on (but it complained when I didn't supply a sub-query)!  Thanks!  I forgot about the issueFieldMatch! 
This is the solution:

issueFunction in issueFieldExactMatch("issuetype = Defect","External ID"," ")

 

For reference, what I had to change was the correct arguments for that field:

issueFieldExactMatch (Subquery, Field name, Field value)

or ... well ... okay it's close enough and on the right track?  I just created a Defect issue with a whitespace External ID, yet the query didn't find it .. hmmm....   At least the query is compiling though

Oh and i couldn't do "^\s" as it said that wasn't an allowed character (i've got scriptrunner 4.3.4)

Quite right! Should have doubled the backslash. "^\\s*$" should mean "start of line, any amount of whitespace characters (including zero), then end of line".

https://scriptrunner.adaptavist.com/latest/jira/jql-functions.html#_issuefieldmatch

@Jonny Carter [Adaptavist] so while your modified query (had to add a sub-query to yours) does actually compile, it still doesn't actually match the one test Defect issue I have with the External ID single line text field with " " single whitespace as only entry.  Can you try it out on your own system with a similar single line text field for any version type?

issueFunction in issueFieldExactMatch("issuetype = Defect","External ID","^\\s*$")

I was able to get it to work on a test JIRA instance with a simple Text Field. My original regular expression included fields where that field was empty, but the following got just the issue where the field was nothing but whitespace characters:

issueFunction in issueFieldMatch("", "Emptyable field", "^\\s+$")

You might try removing the subquery and any other qualifiers you have on the query you're running. Perhaps one of them is filtering out the stuff you want?

issuetype = Defect and ("External ID" is EMPTY or "External ID" = " ")
or maybe
issuetype = Defect and "External ID" in (EMPTY, " ")   <- or any other  characters you can think of.. because next they will add a "." to get around any mandatory field checking!

It seemed like it could be on the right path, but the "=" nor "in" operators can be used with the standard single line text field. 
Sorry for not providing the context of what field type "External ID" was; I thought I had.  Thanks though.

Export all to excel? And do a find and replace ...

Re-import back in via CSV 

You may want to convert it into a SQL query, filter by the EXTERNAL_ID column and then convert it back into a JQL again.

Update:

As "External ID" is a single line text field, it only supports the is, is not, ~, and !~ operators.

Also, "External ID" ~ " " returns the JQL error:

The field 'External ID' does not support searching for an empty string.

Suggest an answer

Log in or Sign up to answer
Community showcase
Asked Thursday in Jira Ops

I'm John Allspaw, Ask Me Anything about incident analysis and postmortems

I'm John Allspaw, co-founder of   Adaptive Capacity Labs, where we help teams use their incidents to learn and improve. We bring research-driven methods and approaches to drive effective inciden...

5,372 views 21 17
View question

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you