I have a database with records in varchar I want to order. Currently, I use the following query to sort by numbers:
SELECT name
FROM table
ORDER BY
NULLIF(regexp_replace(name, E'\\D', '', 'g'), '')::int
Mixed records (numbers + letters) are ordered correctly, but records with only letters are not properly sorted:
Query Results:
name:
1st guy
2nd guy
3rd guy
10th guy
11th guy
v guy
a guy
z guy
c guy
While the result I want is:
Query Results:
name:
1st guy
2nd guy
3rd guy
10th guy
11th guy
a guy
c guy
v guy
z guy
Can you guys help me?
The values that have no numbers all result in a NULL value for the ORDER BY. The order of those rows is undefined as they all have the same "value" to be sorted by. You need to add the name column as a second sort criteria. You probably also want to make sure that the NULL values from the first expression are sorted at the end:
ORDER BY NULLIF(regexp_replace(name, E'\\D', '', 'g'), '')::int NULLS LAST, name