Sorting text column by numbers with letters

Question

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?

Answer 1

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

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.

Answer 2

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]$)')

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

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

Answer 3

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,'_')