Sql - Natural Ordering - Varchar

3

Situation

I need to perform the natural ordering of the string.

Example

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

Order Pattern. (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 Order

    
asked by anonymous 15.10.2015 / 16:53

2 answers

3

The solution I found in the OS was this

Code

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

Explanation

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

Default sort order
Natural ordering example

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