Why do we fail to retrieve data records using some common text as filter criteria?

[Andy Pei _NSB_]

In order retrieve some data records, we use some approximation on specific text fields.  However, it always fails to provide what are existing there.

For example,  when searching all the data records with certain phrases {"abc-de-f1", "abc-de-f2", "abc-de-f3", ....} inside "text field#1", the following JQL syntax has been used.  However, it cannot show correct results.

"text field#1" ~ "abc-de-f*"

[Bill Sheboy · Accepted solution]

Hi Andy -- Welcome to the Atlassian Community!

When using the contains operator ~ have you tried using the exact-phrase syntax to enclose the expression in escaped quotation marks?

"text field#1" ~ "\"abc-de-f\""

https://support.atlassian.com/jira-software-cloud/docs/jql-operators/#CONTAINS----

Kind regards,
Bill

[Andy Pei _NSB_]

Hi Bill,

Thanks a lot for your kind help!

I have just tried your solution. Unfortunately it doesn't work as expected. Only 1 out of 30 data records could be retrieved.  However, it is better than our original approach which returns nothing. 

Still looking for the root cause and working solutions.

Best Regards,
Andy

[Bill Sheboy]

Would you please post some examples of issues' text which matches and does not?  This may reveal some pattern to the symptom.

And also...the hyphen is the negation operator for JQL, but that should not be impacting this as you have it within the escaped-quoted expression.

[Andy Pei _NSB_]

Hi Bill,

Sure, thanks again! Please find here some example:

In some JIRA database, there are a large list of data records with “Issue ID” field as key index.  Current dilemma occurs when we try to retrieve some sub-set with certain values inside “Issue ID” field.

• FEAT0011-AA-A_DOMAIN_1
• FEAT0011-AA-A_DOMAIN_2
• FEAT0011-AA-A_DOMAIN_3
• …….
• FEAT0011-AA-B_DOMAIN_2
• FEAT0011-AA-B_DOMAIN_5
• FEAT0011-AA-C_DOMAIN_1
• FEAT0011-AA-D_DOMAIN_4
• ……
• FEAT0205-AB-A_DOMAIN_1
• FEAT0205-AB-A_DOMAIN_3
• ……

For instance, let us suppose current target is all the data records whose “Issue ID” field contains “FEAT0011-AA-A”. 

But when using the following two JQL phrases, we cannot retrieve what we need.

1. “Issue ID” ~ “FEAT0011-AA-A*”  // No data record can be found
2. “Issue ID” ~ “\”FEAT0011-AA-A\””  // Only 1 data record can be found, always “FEAT0011-AA-A_DOMAIN_1”

If using the following JQL phrase, we would get more than what we need.

• "Issue ID" ~ "FEAT0011-AA*"  //  Not only "FEAT0011-AA-A_xxxxx", but all the other data records like "FEAT0011-AA-B_xxxxx", "FEAT0011-AA-C_xxxxx"

It appears to be quite strange.

Best Regards,

Andy

[Bill Sheboy]

I do not believe you can mix the wildcards and exact-phrase syntax, and I wonder if this is still related to the hyphen or underscores in the field data. 

If you are on a paid Jira license, consider working with your site admin to submit a ticket to support to see what they think is the problem: https://support.atlassian.com/contact/#/

[Andy Pei _NSB_]

Thanks a lot, Bill!  I will move on to check with our site admin.  Any way, it is too late to change the setting rule of "Issue ID" where the hyphen and underscore are widely used:)  We will have to find appropriate solution for that.