Advance search custom field with !~

We have several custom fields and we are trying to search one particular field that contains values that look like TP001, TP150 etc. I am trying to do an advance search on the field using !~ "TP" so that I dont get records that have those values but the search still produces records that contatin the "TP" value. Any ideas?

3 answers

1 vote
Andy Nguyen Atlassian Team Feb 13, 2014

Hi Jose,

If you use !~ "TP" the exact word TP will be searched for (without any characters in front or behind) hence something like TP001 does not match (thus still appears in the search result). You should use !~ "TP*" instead so that the query will search for all phrases starting with TP and exclude them accordingly.

Cheers,

Andy

Hi Andy, yes I've tried using !~ "TP*" and that gives me no results when I know I have records that don't have the TP in the field. Jose
Andy Nguyen Atlassian Team Feb 14, 2014

Hi Jose,

It'd be good if you can let me know exactly which type the custom field is and provide me with several examples of its full values: which one should be returned from the query and which one should not.

Cheers,

Andy

Hello,

It's a custom field, Text Field (single line) called CCSDItemNum.

Examples of values that are in the field are TP015, TP061, TP075 or blank. We want the query to return records where the CCSDItemNum field is blank.

Thank you

Jose

Hi Jose,

if you want those issues where the field has no value, use

CCSDItemNum is empty

Cheers,

Udo

That does work thank you but what happens if we start entering in values of TD001 etc or some other variation of letters and numbers and we only want the ones that start with TP?

you could create a filter (filter1) that shows the issues which starts with TP like

CCSDItemNum ~"TP*"

and then use a negation of that filter

not filter = filter1

0 vote
Andy Nguyen Atlassian Team Feb 17, 2014

Hi Jose,

I think you're not consistent with what you want to achieve hence it's very hard for us to understand and help you with your request.

At first you said you'd like to exclude values starting with TP so I believe CCSDItemNum !~ "TP*" should work.

Later you said you want empty values so, as Udo suggested, CCSDItemNum is Empty should work. If I'm not mistaken, however, what you really want is values not starting with TP or empty values. If that's the case, CCSDItemNum !~ "TP*" OR CCSDItemNum is Empty will work.

Just now you said you only want values starting with TP, then I think CCSDItemNum ~ "TP*" should be enough. If you still want empty values, then just append OR CCSDItemNum is Empty.

I know you may want to experiment with different JQL queries but, if you change your request all the time, we may be confused and cannot follow. Please be consistent with one request first and we'll work out the others. For your information, the only operators available for Text Field (single line) are ~ (text), !~ (text), is (empty), and is not (empty). You may use * or ? to enhance the queries but remember not to put them at the beginning of the text parameters.

All the best,

Andy

Thank you for your professional advice on how to request assistance. I will figure it out. You may close out this thread.

no answer found as of yet.

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Julia Dillon
Posted Apr 17, 2018 in Jira

Tell us how your team runs on Jira!

Hey Atlassian Community! Today we are launching a bunch of customer stories about the amazing work teams, like Dropbox and Twilio, are doing with Jira. You can check out the stories here. The thi...

795 views 2 19
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