Say you have this data set:
some_column |
---|
abc1 |
abc2 |
abc10 |
def1abc1 |
def2abc1 |
def10abc1 |
def10abc2 |
def10abc10 |
If you now do:
SELECT * FROM my_table ORDER BY some_column
you will get the following result:
some_column |
---|
abc1 |
abc10 |
abc2 |
def10abc1 |
def10abc10 |
def10abc2 |
def1abc1 |
def2abc1 |
which might not be what you want.
Now, if you create the following funtion:
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;
you can use this query:
SELECT * FROM my_table ORDER BY pad_numbers(some_column)
with this result:
some_column |
---|
abc1 |
abc2 |
abc10 |
def1abc1 |
def2abc1 |
def10abc1 |
def10abc2 |
def10abc10 |
Now you got your data sorted alpha-numerically.
Note: I have used PostgreSQL 9.0.