nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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 ?

Comments

Tuncay Senturk _Snapbytes_

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

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_mz9b4z

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.

KAGITHALA BABU ANVESH_z1zl10

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.

Tuncay Senturk _Snapbytes_

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

Tuncay Senturk _Snapbytes_

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.

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 _Snapbytes_ 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 _Snapbytes_

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 _Snapbytes_

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

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 _Snapbytes_ 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 _Snapbytes_

You just need to accept the answer

Rachi Manwal

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

jql

filter

number

conversion

string