# Sorting alphanumeric array numerically using QGIS Field Calculator

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

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:

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?

• good solution, but I am looking for a simple solution through the use of QGIS core functions, THANK YOU Jan 16, 2022 at 8:49

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:

• in case you use array (1,3) in line three, it would not consider the two-digit numbers :-( Jan 16, 2022 at 12:56
• You're right, once again! To include two-digit numbers, it must be either array(1,2,3), or generate_series(1,3). I updated the solution accordingly. Jan 16, 2022 at 13:52
• I did a test with my data, my expression takes 125 seconds, yours double; I also notice that it adds a comma to the end or the beginning of the string. :-( Jan 16, 2022 at 16:45
• Yeah, not a real improvement... Thanks for the feedback. No easy solution available, it seems (no one that improves your expression). Jan 16, 2022 at 16:48
• thanks for your time :-) Jan 16, 2022 at 16:50

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

• I tried the expression with my dataset, my expression takes 125 seconds, yours after 10 minutes I stopped it, it seems too slow.Also it doesn't handle 5/1-5/A cases correctly, I know it's not in the example. Thank you Jan 17, 2022 at 8:19

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

• unfortunately it doesn't handle house numbers with multiple letters, see 2/AX which becomes just 2/A or 29/BAC in` 29/B` Jan 16, 2022 at 8:46
• the problem is in the ascii and char functions which return the value of only one character Jan 16, 2022 at 8:54
• I see. This could be adressed somehow, but would make this rather complex expression even more complex. You have a working expression and are looking for an easier one, so that makes no sense. Jan 16, 2022 at 9:10
• If there is not in the core functions, it is the right time to make one. @Babel is right: "your example is tricky enough". I think the best option is to build a new function for your particular problem. Jan 16, 2022 at 9:28
• github.com/qgis/QGIS/issues/46850 Jan 16, 2022 at 9:37