Sorting alphanumeric array numerically using QGIS Field Calculator

Question

For each street I have a list of house numbers in a column of a CSV file and I need to sort them numerically:

House numbers are separated by -.

name	list	sorted_list
street A	5/A-5-4-8-3-6-9-7-1-10-7/B-2-7/A	1,2,3,4,5,5/A,6,7,7/A,7/B,8,9,10
street B	15-1-2/AX-2-3	1,2,2/AX,3,15

How can I get the sorted_list column?

I used this expression, but I find it complicated and slow;

array_to_string(
array_foreach(
    array_sort(
        with_variable('lista',
            string_to_array("sorted_list" ,'-'),
            array_foreach(generate_series(0, array_length(@lista)-1),
            lpad(regexp_substr( (@lista[@element]),'(\\d+)'),3,'0')
            ||'|'||
            if(regexp_substr((@lista[@element]),'([a-zA-Z/]+)') !='',
               regexp_substr((@lista[@element]),'([a-zA-Z/]+)'),
               ' ')
            ||'|'||
            @lista[@element]))),
regexp_replace( @element,'^.+\\|(.+)$','\\1'))
)

Is there an easier way to use expressions?

---

After a few days of study, my request is to obtain the natural sorting as per Kadir's solution, but using the QGIS core expressions; but I think it is impossible and that the way through code is the only and fastest in terms of performance.

Here is a desired example:

solution	value
list	1-1/A-10-1r-2/10-10/1A2-10/A-1000/C1-SNC - to order
kadir	1,1/A,1r,2/10,10,10/1A2,10/A,1000/C1,SNC
my	SNC,1,1r,1/A,2/10,10,10/1A2,10/A,1000/C1
sort -V	1,1r,1/A,2/10,10,10/1A2,10/A,1000/C1,SNC
sort -n	SNC,1,1/A,1r,2/10,10,10/1A2,10/A,1000/C1
natsort	1,1/A,1r,2/10,10,10/1A2,10/A,1000/C1,SNC

sort -V and sort -n are the linux system solutions
natsort is python module and cli to run natural sort processing

Answer 1

Create a new function using Function Editor.

Copy/paste the following script:

from qgis.core import *
from qgis.gui import *
import re

def natural_sort_key(s):
    ns = re.compile('([0-9]+)')
    return [int(t) if t.isdigit() else t.lower() for t in re.split(ns, s)]  

@qgsfunction(args='auto', group='Custom', usesgeometry=False)
def sort_my_addresses(field, feature, parent):
    _list = feature[field].split("-")
    _list.sort(key=natural_sort_key)
     
    return ','.join(_list)

Click on "Save and Load Functions" button. Use this expression:

sort_my_addresses('list')

Note: the field parameter name (list here) for sort_my_addresses should be between single quotes ('), not double quotes ("). For example, 'field_name', not "field_name".

Reference for natural_sort_key method: How to sort a list containing alphanumeric values?

Answer 2

Two approaches that resolve different parts of the problem and could maybe help finding an easier solution. Approach 3 solves the problem, based on approach 2, but is similarily complex as your initial expression.

Approach 1: array_prioritize

Integer-only elements will be sorted correctly, combined alpha-numerical strings will be put at the end:

array_to_string (
    array_prioritize(
        string_to_array("list" ,'-'), 
        generate_series(1,1000)
    )
)

Approach 2: using map() functions

Correct sorting of integer only and mixed alpha-numerical values, but only of the same length of the numerical part - so refining this to run separately for single-digit, two-digit and three-digit values should return the correct order:

array_to_string (
    array_prioritize(
        map_akeys(
            hstore_to_map( 
                array_to_string(
                    array_foreach (
                        string_to_array("list" ,'-'),
                        @element  ||  '=>'  ||  regexp_substr( @element, ('\\d+'))
                    )
                )
            )
        ), 
        array (1,1000)
    )
)

Approach 3: running approach 2 separately for single/two digit values

Adapting approach 2 from above to take into consideration only single digit house numbers, this expression return the correct alpha-numerical sort order. Change 1 to 2 for two digit values (and so on) in line 10 and concatenate the resulting strings:

array_to_string (
    array_prioritize(
        map_akeys(
            hstore_to_map( 
                array_to_string(
                    array_foreach (
                        array_foreach (
                            string_to_array("list" ,'-'),
                            if (
                                length (regexp_substr( @element, ('\\d+'))) =1,  -- no. of digits
                                @element, ''
                            )
                        ),
                        @element  ||  '=>'  ||  regexp_substr( @element, ('\\d+'))
                    )
                )
            )
        ), 
        array (1,1000)
    )
)

To avoid repaeting the above expression for each length (no. o digits), again add an array_for_each() function. Line 3 defines the no. of digits for which the expression should run - here for 1- and 2-digit lengths:

array_to_string (
    array_foreach (
        array(1,2), -- no. of digits to take into consideration
        with_variable(
            'digits',
            @element,
            array_to_string (
                array_prioritize(
                    map_akeys(
                        hstore_to_map( 
                            array_to_string(
                                array_foreach (
                                    array_foreach (
                                        string_to_array("list" ,'-'),
                                        if (
                                            length (regexp_substr( @element, ('\\d+'))) = @digits,
                                            @element, ''
                                        )
                                    ),
                                    @element  ||  '=>'  ||  regexp_substr( @element, ('\\d+'))
                                )
                            )
                        )
                    ), 
                    array (1,1000)
                )
            )
        )
    )
)

And, changing line 3 either to array(1,2,3), or to generate_series(1,3,1), three-digit values are also sorted correctly:

Answer 3

A slightly different approach to solving the problem from the solutions till now proposed is the following expression:

array_to_string(
    array_foreach(
        array_sort(
            array_foreach(
                string_to_array(
                    replace('5/A-5-16/B-4-18-15-22-3-14-12-6-16/A-16/FG-9-11-13-7-21-1-19-10-7/A-17-123-20','-',',')),
                lpad(@element,((max_length(regexp_substr(@element,'(\\d+)')))+3)+length(regexp_replace(@element,'\\d+','')),0)
            )   
        ),
    regexp_replace(regexp_replace(@element, '^00',''),'^0',''))
)

With this expression, both elements with one or more letters and numbers with more than two-digit are processed and ordered correctly.

Answer 4

A slightly different approach, converting the characters to its ascii code and adding it as decimals to the house numbers, creating decimal numbers that are then sorted and afterwards coverted back to the initial format:

array_to_string (
    array_foreach (
        array_sort (
            array_foreach (
                string_to_array (list,'-'),
                to_real (
                    concat (
                        regexp_substr (
                            @element,   
                            ('\\d+')
                        ),  
                        '.' ,
                        ascii(
                            regexp_substr (
                                replace (@element, '/',''),
                                ('\\D+')
                            )
                        )
                    )
                )
            )
        ),
        floor(@element)  || 
        if (@element > floor (@element), '/', '') || 
        char (100*round (@element -floor(@element),2))
    )
)