Filter to find issues with a comma in a Custom Field

[Deleted user]

Hi,

We use a text Custom Field to store a charge numbers. This field must contain a comma to be a valid charge number. I want to find issues with invalid charge numbers so I wrote this filter:

"Charge Number" !~ ','

The filter query is valid but it returns every issues that have a value in the Charge Number field. On the other hand

"Charge Number" ~ ','

returns nothing.

I tried using the unicode number (\u002c) with the same results. 

Do you know how to write this query?

Thanks!

[Vasiliy Zverev · Accepted solution]

There is a trouble for JIRA to search with ~ when firt simbols are not spesified (https://confluence.atlassian.com/jira/performing-text-searches-185729616.html and https://jira.atlassian.com/browse/JRA-6218)

For out needs I use this SQL statement (for MS SQL )

Select
      project.pkey + '-' + CAST( jiraissue.issuenum as varchar(20)) as issueKey
      , customfieldvalue.*
from
      customfield 
      join customfieldvalue   
            join jiraissue    
                  join project      on    jiraissue.PROJECT = project.ID
            on    customfieldvalue.ISSUE = jiraissue.ID
      on customfield.ID = customfieldvalue.CUSTOMFIELD
where
      customfield.cfname = '№ док'
      and customfieldvalue.STRINGVALUE like '%,%' --

[Deleted user]

Hi, thanks a lot for your answer! However, I set up a filter to catch issues with missing info or bad formatting that sends an email to my team and this SQL query will not work with my filter. I would have liked something in JQL... I will think about this.

[J. Caldwell]

Per Vasiliy's comment, you'll want to look at the JRA issue. It references a work around using Scriptrunner. If you already have it, awesome, should be good to go. If not, you'll need to look into getting it. 

[Deleted user]

We already use Scriptrunner so it should be feasible. I will look into it. Thanks a lot to both of you for your help!

[J. Caldwell]

Also...if you already have script runner, I'd see if you could put in a validator/condition checker to prevent bad data from getting in there.

[MattS]

I think I'd step back and see where the invalid data is coming from. Prevent the field from getting it in the first place