9

I have a text column. It's made up of the numbers 1, 2, 3 up to 1650, which are currently not in increasing order. However, it also includes text such as 520a, 639a, 639b, 639c. Sorting on increasing value would produce 1, 10, 100, 1000, etc, rather than 1, 2, 3, ...

I know how to convert from text to integer, which would work if my cells did not include alpha characters.

How can I sort to end up with 1, 2, 3, ..., 10, ..., 100, ..., 519, 520a, 521, ..., 638, 639a, 639b, 639c, 640, ..., 1650?

9

you could try creating a new decimal column in field calculator with a precision of 2 and use the following if statement in the expression window:

if (upper("yourTextField")= lower("yourTextField"),"yourTextField",
regexp_substr("yourTextField",'(\\d+)')+(strpos( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
upper(right("yourTextField",1)))/100))

enter image description here

It tests to see if there is a letter in your text field - if not then use the original value, if so it uses regexp_substr to extract the number part and then adds the strpos value divided by 100 to make the value added less than one. The following image shows an example of the outputs.

enter image description here

| improve this answer | |
  • Many thanks for the two replies. I tried Kazuhito's answer first, but the field calculator said there was a problem with the function (despite copy and paste) Then I tried Ian's, and it worked. Problem solved! – Alasdair Oct 25 at 17:50
  • 1
    This is really the most elegant and simple solution, using nothing but QGIS functions. I'll have to shelve this is the back of my mind for when I need it. – Gabriel C. Oct 26 at 12:32
  • 2
    You can also do this directly by sneaking to @JGHs answer: right click the yourTextField-column in the attribute table, click sort and add to_real() to this expression: to_real(if (upper("yourTextField")= lower("yourTextField"),"yourTextField", regexp_substr("yourTextField",'(\\d+)')+(strpos( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', upper(right("yourTextField",1)))/100))) – MrXsquared Oct 26 at 19:00
  • @ian Brilliant! – Kazuhito Oct 27 at 10:15
  • 1
    @Alasdair then accept the answer with the checkbox – BERA Oct 28 at 18:23
7

I recommend Sort and Number plugin, which can take multiple fields as sorting criteria.

(1) Preparation

To allow the plugin to recognize the number part and the character part of your textcolumn, create two separate columns by expressions in the Field Calculator.

num_part (integer): regexp_substr("textcolumn", '(\\d+)')

char_part (text): regexp_substr("textcolumn", '([a-z]$)')

enter image description here

(2) Run Sort and Number plugin. It will create a new order field.

enter image description here

(3) Sort by the order field. You will see something like below.

enter image description here

| improve this answer | |
6

Extending on @Kazuhito preparation step, you can achieve such sort directly.

Open the attribute table, right click on a field column name, choose sort

Then enter the following code, replacing the column name by your own. Change the 10 with the max number of digits / letters.

Basically, this code extracts the number, adds 0s before so every number is exactly 10 digits, then it does the same with the text part, adding _ so each text is exactly 10 characters long. Then is aggregates both together using ||. Since every entry is 0 and _ left padded, a regular text sorting can now be applied.

 lpad( regexp_substr("id", '(\\d+)'),10,'0')
||
 lpad( regexp_substr("id", '([a-z]$)'),10,'_')

enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.