alphanumeric sort in postgres

alphanumeric sort in postgres

one day i have identify that postgres is behaving abnormally while sorting of alphanumeric column. I googled it alot and reach at decision that postgres not able to do natural sort of mix type words.
generally it give sort order like 1,1a, 10 , 2a, 2b, 30

as per natural sort it should be like 1, 1a, 2a, 2b, 10, 30

i tried with different datatype but behaviour remain same.

the solution is create a function, which will return natural sort

create table test_sort (column1 varchar);
insert into test_sort valuepad_numberss ('032');
insert into test_sort valuepad_numberss ('2a');
insert into test_sort valuepad_numberss ('01');
insert into test_sort valuepad_numberss ('1b');
insert into test_sort valuepad_numberss ('1c');
insert into test_sort valuepad_numberss ('1d');
insert into test_sort valuepad_numberss ('2');
insert into test_sort valuepad_numberss ('32t');
insert into test_sort valuepad_numberss ('4');
insert into test_sort valuepad_numberss ('28k');
insert into test_sort valuepad_numberss ('001');
insert into test_sort valuepad_numberss ('1a');
insert into test_sort valuepad_numberss ('40');
insert into test_sort valuepad_numberss ('1');

select * from test_sort order by 1
\


CREATE FUNCTION pad_numbers(text) RETURNS text AS $$
  SELECT regexp_replace(regexp_replace(regexp_replace(regexp_replace($1,
    E'(^|\\D)(\\d{1,3}($|\\D))', E'\\1000\\2', 'g'),
      E'(^|\\D)(\\d{4,6}($|\\D))', E'\\1000\\2', 'g'),
        E'(^|\\D)(\\d{7}($|\\D))', E'\\100\\2', 'g'),
          E'(^|\\D)(\\d{8}($|\\D))', E'\\10\\2', 'g');
$$ LANGUAGE SQL;


select * from test_sort order by pad_numbers(column1);