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


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


  • 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

This widget could not be displayed.

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".

@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?

This widget could not be displayed.
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.

This widget could not be displayed.

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

Re-import back in via CSV 

This widget could not be displayed.

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.

This widget could not be displayed.


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
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Wednesday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

106 views 2 0
Join discussion

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