PostgreSQL: Sorting letters before numbers

Question

I would like to order a bpchar column in the following order (first order by a-z, then numbers):

abc
bcd     
xrf/1     
zyd   
0/abc
0/bdc   
0/efg

How could I accomplish that?

Thank you

Kaushik Nayak · Accepted Answer · 2018-11-16 11:47:43Z

6

Can't fully tell from your question what you actually want. If it is the first character of the string you want to check whether it's numeric or alphabet, you may use a CASE expression in ORDER BY like this.

select * FROM t ORDER BY
          CASE 
             WHEN col ~ '^[a-zA-Z]' THEN 1
             WHEN col ~ '^[0-9]'    THEN 2
           END,col;

Demo

answered Nov 16, 2018 at 11:47

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: Sorting letters before numbers

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