CODEWITHSUNDEEP
sqlpostgresqlnatural-sort
Vineeth V
Vineeth V

Reputation: 35

Alphanumeric Sorting in PostgreSQL 9.4

I've a table in PostgreSQL 9.4 database in which one of the column contains data both integer and alphabets in following format. 

1
10
10A
10A1
1A
1A1
1A1A
1B
1C
1C1
2
65
89

Format is, it starts with a number then an alphabet then number then alphabet and it goes on. I want to sort the field like below, 

1
1A
1A1
1A1A
1B
1C
1C1
2
10
10A
10A1
65
89

But when sorting 10 comes before 2. Please suggest a possible query to obtain desired result.

Thanks in advance

Upvotes: 1

Views: 1050

Answers (1)

Anuraag Veerapaneni
Anuraag Veerapaneni

Reputation: 679

Try this

SELECT *
FROM table_name
ORDER  BY (substring(column_name, '^[0-9]+'))::int     -- cast to integer
          ,coalesce(substring(column_name, '[^0-9_].*$'),'')

Upvotes: 1

Related Questions