How to sort by numeric and alphanumeric items in postgresql

Question

i want to sort a list that has numeric and alphanumeric items like this:

And this is how i want to stay:

But, when i use the 'order by' clause, the sort appears like this:

That order is because the sentence detects a string list, and the natural sort for strings mades it with alphabetical order

I don't know how to separate numeric with strings for sort, first with numeric and second with alphanumeric order

Could you send me wich is the duplicate question? I was finding a lot of questions, but none works — D. Vazquez, Sep 22 '15 at 07:50
Try http://2kan.tumblr.com/post/361326656/postgres-natural-ordering — Vivek S., Sep 22 '15 at 08:16

score 0 · Answer 1 · edited May 23 '17 at 12:22

0

Is a a natural Sort what yiou want ? Try using Order by Length(column), column.

In Fact this is just the Short Version that does not always works as expected. If you want the REAL natural sort, use this: Natural Sort in MySQL

edited May 23 '17 at 12:22

Community

answered Sep 22 '15 at 06:38

Alexis Peters

score 0 · Answer 2 · answered Sep 22 '15 at 06:41

0

You can try to cast your alphanumeric column to bytea datatype and try like this:

SELECT colname
FROM tablename
ORDER BY colname::bytea;

answered Sep 22 '15 at 06:41

Rahul Tripathi

1

This solution i found it, but returns the same third list shown – D. Vazquez Sep 22 '15 at 07:52

2 Answers2