Alphanumeric sorting with PostgreSQL

Question

In the database, I have various alpha-numeric strings in the following format:

10_asdaasda
100_inkskabsjd
11_kancaascjas
45_aksndsialcn
22_dsdaskjca
100_skdnascbka

I want them to essentially be sorted by the number in front of the string and then the string name itself, but of course, characters are compared one by one and so the result of Order by name produces:

10_asdaasda
100_inkskabsjd
100_skdnascbka
11_kancaascjas
22_dsdaskjca
45_aksndsialcn

instead of the order I'd prefer:

10_asdaasda
11_kancaascjas
22_dsdaskjca
45_aksndsialcn
100_inkskabsjd
100_skdnascbka

Honestly, I would be fine if the strings were just sorted by the number in front. I'm not too familiar with PostgreSQL, so I wasn't sure what the best way to do this would be. I'd appreciate any help!

Unfortunately, PostgreSQL doesn't offer automatic number collation, nor date collation ("Jan" < "Feb < "Apr" etc). It's really complicated to do and would have to be set per-column, per-query or per-sort even as it often isn't desired and would be expensive to do unncessarily. AFAIK nobody's implemented it yet. It'd be possible with a custom text data type variant like citext does for case insensitivity, it's just a matter of the (lots of) coding getting done by somebody who cares. — Craig Ringer
– Craig Ringer, Commented Jul 11, 2012 at 0:42

Erwin Brandstetter · Accepted Answer · 2022-01-02 21:34:15Z

46

The ideal way would be to normalize your design and split the two components of the column into two separate columns. One of type integer, one text.

With the current table, you could:

SELECT col
FROM   tbl
ORDER  BY (substring(col, '^[0-9]+'))::int  -- cast to integer
         , substring(col, '[^0-9_].*$');    -- works as text

The same substring() expressions can be used to split the column.

These regular expressions are somewhat fault tolerant:
The first regex picks the longest numeric string from the left, NULL if no digits are found, so the cast to integer can't go wrong.
The second regex picks the rest of the string from the first character that is not a digit or '_'.

If the underscore (_) is an unambiguous separator, split_part() is faster:

SELECT col
FROM   tbl
ORDER  BY split_part(col, '_', 1)::int
        , split_part(col, '_', 2);

db<>fiddle here

See:

Split comma separated column data into additional columns

edited Jan 2, 2022 at 21:34

answered Jul 10, 2012 at 16:58

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1464055 Over a year ago

So, if I had a query like...SELECT name FROM nametable, how exactly would I put it into that? If the names were each of the strings. Would it be something like WITH x(t) AS name, and etc?

Erwin Brandstetter Over a year ago

@user1464055: The CTE is just for demonstration here on the site and easy testing. If x was a table and t the column (and without the CTE), it would work the same. I added the exact syntax and some more details.

user1464055 Over a year ago

Thanks a lot, that was a great answer to my question. I wanted to do something very similar, but I just couldn't figure out the syntax for the life of me. Thanks again!

Erwin Brandstetter Over a year ago

@user1464055: Regular expressions are powerful but tricky. I added another tiny improvement.

user1464055 Over a year ago

Yeah, I was trying to pretty much cast everything, and obviously that was failing. I understand both of the expressions now, thanks for giving me a perfect workaround to adding another column! Haha, honestly, I didn't make the database, and adding a column may have been a challenge.

D.J. · Accepted Answer · 2013-06-21 18:33:40Z

9

You can use regular expressions with substrings

   order by substring(column, '^[0-9]+')::int, substring(column, '[^0-9]*$')

answered Jun 21, 2013 at 18:33

D.J.

872 silver badges2 bronze badges

Comments

Marcelo Zabani · Accepted Answer · 2012-08-12 17:34:58Z

4

There is a way to do it with an index over an expression. It wouldn't be my preferred solution (I would go for Brad's) but you can create an index on the following expression (there are more ways to do it):

CREATE INDEX idx_name ON table (CAST(SPLIT_PART(columname, '_', 1) AS integer));

Then you can search and order by CAST(SPLIT_PART(columname, '_', 1) AS integer) every time you need the number before the underline character, such as:

SELECT * FROM table ORDER BY CAST(SPLIT_PART(columname, '_', 1) AS integer);

You can do the same to the string part by creating an index on SPLIT_PART(columname, '_', 2), and then sort accordingly as well.
As I said, however, I find this solution very ugly. I would definitely go with two other columns (one for the number and one for the string), then maybe even removing the column you mention here.

edited Aug 12, 2012 at 17:34

answered Jul 10, 2012 at 17:03

Marcelo Zabani

2,3191 gold badge20 silver badges28 bronze badges

1 Comment

Erwin Brandstetter Over a year ago

+1 An index on the expression is a good idea to improve sort performance. Faster yet: a multi-column index on both expressions - in matching order. (Reposted comment to fix link format).

Brad · Accepted Answer · 2012-07-10 16:53:26Z

1

You should add a new column to the database which is has numeric data type and on persisting a new record set it to the same value as the prefix on the string value you have.

Then you can create an index on the properly typed numeric column for sorting.

answered Jul 10, 2012 at 16:53

Brad

16k11 gold badges62 silver badges75 bronze badges

1 Comment

user1464055 Over a year ago

Yeah, that's honestly what I was thinking. Otherwise, it's pretty challenging to figure out how to sort these. I was just hoping for another way since there's already like 600 elements in this format :/

Collectives™ on Stack Overflow

Alphanumeric sorting with PostgreSQL

4 Answers 4

5 Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related