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

[Geoff Wilson]

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

[Jonny Carter · Accepted solution]

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$"))

[Geoff Wilson]

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)

[Geoff Wilson]

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)

[Jonny Carter]

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

[Geoff Wilson]

@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*$")

[Jonny Carter]

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?

[Jeff Louwerse]

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!


[Geoff Wilson]

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.

[Geoff Wilson]

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.

[Pablo Beltran]

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.

[Darren Pegg]

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

[Darren Pegg]

Re-import back in via CSV