SQL and alpha-numeric sort order

Say you have this data set:

my_table
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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>