Add sql function for natural order by

[hregis]

Feature Request

Use a sql function for natural order by

Use case

No response

Suggested implementation

I propose to add these sql functions to Dolibarr in order to have lists ordered in a natural order

natsort copie.txt

TRUC001
TRUC002
TRUC010

instead

TRUC001
TRUC010
TRUC002

Suggested steps

you can test this sql functions with :

$sql.= " ORDER BY udf_NaturalSortFormat(".$sortfield.", 10, '.') ".$sortorder.";

remains to make these functions compatible with Postgresql...
which is not currently the case in the creation syntax

[hregis]

this is can be include in "order" function of DoliDB class with constant and parameter

[hansemschnokeloch]

Natural_sort_key is implemented in MariaDB since version 10.7 (but not in mysql)
https://mariadb.com/kb/en/natural_sort_key/

[hregis]

@hansemschnokeloch @eldy maybe add a function in the meantime to use either the MariaDB function or the function I propose?

https://modern-sql.com/caniuse/natural_sort_key

[eldy]

@hansemschnokeloch @eldy maybe add a function in the meantime to use either the MariaDB function or the function I propose?

https://modern-sql.com/caniuse/natural_sort_key

Pb is that support will not be available on all installation. So i suggest to first introduce this as a hidden constant into the order method.