Order by alphabet and then by numbers I have records like

 A5
 A4
 Z1
 B2
 C7
 C1A
 C11A
 B1
 B4


I want them to be sorted in this manner

A4
A5
B1
B2
B4
C1
C11A
C7
Z1


using the ORDER BY clause.

I want them to be sorted by alphabets and then by numeric values.
For your request:


  sorted by alphabets and then by numeric values


I assume (deriving from your sample data) you want to ORDER BY:


The first letter, treated as text.
The first number (consecutive digits), treated as integer.
The whole string to break remaining ties, treated as text. May or may not be needed.




SELECT *
FROM   tbl
ORDER  BY left(col, 1)  -- 1st letter as text
     , substring(fest, '\d+')::int NULLS FIRST  -- first number in string as int
     , col  -- whole columns as cheap tiebreaker


SQL Fiddle.

More details:


Alphanumeric sorting with PostgreSQL
Humanized or natural number sorting of mixed word-and-number strings