Convert String to Number on JQL Jira Filter

[Baris TEKELI]

Hello

I have a field as a string, which contains a number (Yes i know... i didnt create it :x)

In the filter i want to create, i need to order the result by this field.

Something like this : 

project = MyProject AND resolution IS empty ORDER BY stringField

"stringField" is the field i want to cast as a number.

This field contains number like : 1, 2, 3, ..., 10, 11, ..., 40, 41, ...

Jira orders it like this : 1, 10, 11, 12, 13, ..., 2, 21, 22, 23, ...

I tried few ways founded in Google, but nothing worked for me.

Can anyone would be able to help me ?

[Tuncay Senturk]

Hi @Baris TEKELI 

Welcome to the community!

JQL ordering is done according to the appropriate searcher, and in your case, it is done via String Searcher. So, the result you're seeing is the correct result. 

You can't do a number sorting on string field. The best thing to do is to create a number field, convert the string values to number values and bulk update the new field accordingly.

Then your JQL will order the results in a correct manner.

I hope it makes sense.

Tuncay

[Tuncay Senturk]

Bear in mind that workarounds generally will be a pain in the neck in the future. So, it would be wise to create a number field and copy the values from the text field (if it is not too late).

[Chris Annal]

Other benefits of this suggestion are (once you're satisfied that all instances have been successfully converted):

This should prevent "illegal" entries, such as alphanumeric entries where only numbers should be allowed.

Prevent "whitespace" in the field. (This can be troublesome if sharing data. A string "number" with whitespace after it may not be treated as equal to that same string "number" with no whitespace after it).

These are just two benefits that I can think of that should weigh in the decision of creating a new number field, like Tuncay Sunturk_Snapbytes_ described.

[Marc Minten _EVS_]

If for some reason you want to keep your String field, and if you run scriptrunner, create a (numeric) scripted field that shows the contents of your string-field in number format. Ordering on that field will behave as expected...

[Warren]

Hi @Baris TEKELI 

One other thing to try, if you add a leading 0 to the single digit numbers i.e. 01, 02, ... 09 that may get around the issue.

[Tuncay Senturk]

But as far as I understand this is not a field for only two-digit numbers.

String sort will be done as below: 

• 1
• 101
• 1018
• 2
• 23
• 2345
• 3

[KAGITHALA BABU ANVESH]

Hi i Found some old answer from the top activity leader  @Nic Brough -Adaptavist- ,

If the string field is an text field , sorting may not work properly.

If The string Field is an Numeric field , there is a chance of displaying results correct order.

If the string field is numeric , and sorting not worked, and you are using "Jira Server". you can re-index the Jira. this will helps you.

[Amir Katz (Outseer)]

I agree that a new numeric field is the best solution. There are two ways to go forward:

1. Create the new field, run some automation/scripting on all existing issues to bulk-copy the old field to the new one, then modify the new-issue/edit-issue screens to hide the old field and show only the new one. Basically, you're deprecating the old field (if it's mandatory, make it not so).

2. Add the new field, hide it, add some automation to populate the new one whenever a new issue is created or when an existing issue gets updated - you can do it with an event listener.

My preference - option 1 - let bygones be bygones :-)

[Bill Sheboy]

Hi @Baris TEKELI  -- Welcome to the Atlassian Community!

In addition to the other suggestions...

How many times do you need to sort the information in this way, and does the result need to stay inside of Jira for follow-up actions?

• If it is a few sorts, you could just export the data to a spreadsheet and convert prior to sorting
• If this is ongoing need, you could try the Jira Excel or Google Sheets add-ons, pull the data, convert to a number, and then sort

Best regards,

Bill

[Baris TEKELI]

Hello, and thank you so much for your answers !

I read your answers and noticed that I was not clear enough.

Few details : 

• Indeed, this is not a field for only two-digit numbers, but 3 digits like @Tuncay Senturk  said. It can reach 124.
• I'ld like to use this field as a scoring field. It will set a scoring for many Jiras according to they priority. And I want to order by this field to have a better view in the board I use to show the Jiras to process to my Team. So This filter is use every single days, many times.
• And like many of you suggested, I'ld love to convert this field into a numeric one, since i (or my organisation) dont really need it as a String. I'ld also love to create a new numeric field to fullfill my needs. But I don't have the control to create a new field.
  (I tried to ask to update the type of this field, but they don't know how to do that, and i can't get the rights to do it myself)

This is why I'm trying to convert this String field into a number one.

The best way i found so far is to create many filter result in my board.

I still order by this stringField, but i added a condition to take the stringField ten by ten : 

• Filter 1 : stringField from 1 to 9 ; 
• Filter 2 : from 10 to 19 ; 
• Filter 3 : from 20 to 29 ; 
• Filter 4 : from 30 to 39 ; 
• Filter 5 : from 40 to 49 ; 
• Filter 6 : bigger than 50 ; 

And the condition is like this (for the filter 1) : 

---

stringField ~ "1 OR 2 OR 3 OR 4 OR 5 OR 6 OR 7 OR 8 OR 9" ORDER BY stringField DESC

---

Waiting to found how to convert the stringField, if it's possible, or waiting to get my rights to do it myselft. (or create a new field).

Thank you again for your answers !

[Tuncay Senturk]

Hmm even though I do not recommend, you can bulk update these issues adding leading zeros as Warren mentioned.

• filter issues which have values 1 to 9 and update them adding leading zeros (001 to 009)
• filter issues which have values 10 to 99 and update adding leading zero (010 to 099)
• the rest (> 100) are OK.

then you can use sorting.

[Baris TEKELI]

I can see that it's a good solution.

But how exactly do i add a leading 0 only for 2 digits, and two 0 for 1 digits ?
And then, how do i sort according to this ?

[Tuncay Senturk]

You can do that programmatically. If you have Script Runner you can easily code a few lines and do the bulk update.

• Get the custom field value
• Convert it to a number
• If value < 10 then the strValue = "00" + value
• If value >= 10 and value  < 100 then the strValue = "0" + value
• else no change

I can help you on this code but as I mentioned before this is dirty workaround, the best is to use number field.

Cheers

[Baris TEKELI]

Thank you @Tuncay Senturk 

The code is not a problem.

The problem is that I dont have Script Runner, and I tried to do it via JQL Filter (lol)

I know that the best way is to create a new field, or change the existing one. I'll try this way with my organisation. Hopefully, they will understand my need.

[Chris Annal]

Not to digress from the topic here, but if I had one single Add-On to purchase for Jira, it would be Script Runner. You'll find a LOT of solutions offered refer to running a script to do something and it's the best tool for doing that. It also has a lot of built-in tools and scripts that allow you to perform many Administrative tasks.

I'm not a Script Runner salesman, just a Jira administrator like you who has to address a lot of users who want Jira to do this or that...

Good Luck!

:)

[Amir Katz (Outseer)]

I wholeheartedly support what @Chris Annal said above. I am also not receiving any commission from Adaptavist :-).

To me, ScriptRunner has proven itself in at least two categories:

1. Provide solutions to problems that Atlassian should have but never did. An example is the built-in script to bulk-import custom field values (for single/cascaded selection list fields) and the bulk resolution setter.

2. Save you a lot of manual work and/or Java programming. A great example is the event listeners. 

Be aware that that there are other add-ons that provide multiple capabilities, such as JMWE and JEP.

[Bill Sheboy]

@Baris TEKELI   I cannot tell from the posts if you are using the Cloud or Server version...In either case...

I you plan to leave the text field in place, and people can continue to edit the field...

Have you considered using a simple automation rule to automatically left-pad the field with zeros?  Then a simple string sort will work.

For example:

• Trigger: stringField has changed 
• Condition: check if it is a number  {{issue.stringField.asNumber}} is greater than 0
• Action: edit stringField with the following smart values

{{issue.stringField.leftPad(3, "0")}}

To learn more about automation rules please look here:

https://support.atlassian.com/jira-software-cloud/docs/automate-your-jira-cloud-processes-and-workflows/

https://www.atlassian.com/software/jira/automation-template-library#/label/all/1453

[Ignacio Pulgar]

Hi @Baris TEKELI ,

Provided that you would like to use such a number as a score for priority, I'd suggest using Jira Software's Rank native feature.

Rank is an alphanumeric field (it effectively works as a number) for convenient reasons (ability to represent really large numbers).

Besides, it also solves the problem of reordering issues with ease (Rank's multiselect + drag & drop VS multiple manual issue editions of a number field).

That said, regarding a method for setting a number field with the content of a text field, I would suggest exporting the issues to CSV through the Issue Navigator, with the following columns: Key, Summary, <Your Scoring Text Field>. After that, the Jira Administrators CSV import feature might be used to update said issues, provided that mapping the issue key of existing issues updates those issues instead of creating new issues. During the import wizard, map issuekey with issuekey, summary with summary and <Your Scoring Text Field> with a number field.

Hope it helps.

[Baris TEKELI]

Hi @Ignacio Pulgar 

Indeed, this could also help !
I'll try to use your suggestion in another issue, since the one in this post is solved.

[Baris TEKELI]

Hi everyone,

I have been able to get a numeric field from my organisation. The text field is no longer used. So i can filter properly with numbers, as @Tuncay Senturk  first said.

And it works fine.

So now, i'ldlike to close/solve this post, but I don't know how..

Again, thank you all for your help !

[Tuncay Senturk]

You just need to accept the answer :) 

[Rachi Manwal]

How can a string be converted to a number in jql?