Sql - Natural Ordering - Varchar

Question

Situation

I need to perform Natural string ordering.

Example

Processo 1
Processo 2
Processo 3
Processo 10
Processo 11
Processo 12

Standard Order. (ASC)

Processo 1
Processo 10
Processo 11
Processo 12
Processo 2
Processo 3

Natural Order

Processo 1
Processo 2
Processo 3
Processo 10
Processo 11
Processo 12

Related

PHP Natural ordering

Answer 1

The solution I found in the OR was this

Code

ORDER BY 
    regexp_replace(column_name, '\D','','g')::integer

Explanation

It will remove from the string anything other than number, then convert to integer, after applying the integer-based ordering.

Pattern ordering example
Example natural ordering

Answer 2

SELECT 
    column_name 
FROM 
    table_name 
ORDER BY
    column_name REGEXP '^\d*[^\da-z&\.\' \-\"\!\@\#\$\%\^\*\(\)\;\:\\,\?\/\~\`\|\_\-]' DESC, 
    column_name + 0, 
    column_name;