Sort varchar with letters and numbers - PostgreSQL

Question

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?

user330315 · Accepted Answer · 2016-09-29 21:10:10Z

2

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

answered Sep 29, 2016 at 21:10

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sort varchar with letters and numbers - PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related