Postgresql sorting mixed alphanumeric data

Question

Running this query:

select name from folders order by name

returns these results:

alphanumeric
a test
test 20
test 19
test 1
test 10

But I expected:

a test
alphanumeric
test 1
test 10
test 19
test 20

What's wrong here?

It looks weird: 'test 20' < 'test 19'. What LC_COLLATE you have by examinating SHOW lc_collate; ? I have en_US.UTF-8 and it returns exactly wanted output with ORDER BY name ASC. — Grzegorz Szpetkowski
– Grzegorz Szpetkowski, Commented Aug 10, 2011 at 22:32
I get the same thing, too. I just did select 'alphanumeric' < 'a test' and got f. — Ray Toal
– Ray Toal, Commented Aug 10, 2011 at 22:34
for the record, i posted an answer pointing you to the manual entry for collation. it was donwvoted as not being huggy-lovey enough so i deleted it. but i think you should start there. — andrew cooke
– andrew cooke, Commented Aug 10, 2011 at 22:37
@andrew cooke: I didn't downvote, that was good point, collation is responsible for ordering, however per-column collation, which could be useful here is supported only since Postgres 9.1. — Grzegorz Szpetkowski
– Grzegorz Szpetkowski, Commented Aug 10, 2011 at 22:41
Hello, thk for your comments... SHOW lc_collate; is returning es_SV.UTF-8 (I live in El Salvador) — el_quick
– el_quick, Commented Aug 10, 2011 at 22:44

Say No To Censorship · Accepted Answer · 2015-10-22 20:54:17Z

29

You can simply cast name column to bytea data type allowing collate-agnostic ordering:

SELECT name
FROM folders
ORDER BY name::bytea;

Result:

     name     
--------------
 a test
 alphanumeric
 test 1
 test 10
 test 19
 test 20
(6 rows)

edited Oct 22, 2015 at 20:54

Say No To Censorship

5371 gold badge15 silver badges32 bronze badges

answered Aug 10, 2011 at 23:17

Grzegorz Szpetkowski

38.1k6 gold badges94 silver badges140 bronze badges

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

2 Comments

user177800 Over a year ago

This fixes sorting with leading spaces that TRIM would not solve.

Raywell Over a year ago

Be careful, as it's still alphanumeric (meaning "3" will come after "20")

A Vlk · Accepted Answer · 2015-09-04 06:49:52Z

24

All of this methods sorted my selection in alphabetical order:

test 1
test 10
test 2
test 20

This solution worked for me (lc_collate: 'ru_RU.UTF8'):

SELECT name
FROM folders
ORDER BY SUBSTRING(name FROM '([0-9]+)')::BIGINT ASC, name;

test 1
test 2
test 10
test 20

answered Sep 4, 2015 at 6:49

A Vlk

2412 silver badges2 bronze badges

Comments

Deepak Pandey · Accepted Answer · 2019-08-09 09:38:09Z

15

select * from "public"."directory" where "directoryId" = 17888 order by
COALESCE(SUBSTRING("name" FROM '^(\d+)')::INTEGER, 99999999),
SUBSTRING("name" FROM '[a-zA-z_-]+'),
COALESCE(SUBSTRING("name" FROM '(\d+)$')::INTEGER, 0),
"name";

NOTE: Escape the regex as you need, in some languages, you will have to add one more "\".

In my Postgres DB, name column contains following, when I use simple order by name query:

1
10
2
21
A
A1
A11
A5
B
B2
B22
B3
M 1
M 11
M 2

Result of Query, After I have modified it:

1
2
10
21
A
A1
A5
A11
B
B2
B3
B22
M 1
M 2
M 11

edited Aug 9, 2019 at 9:38

answered Aug 9, 2019 at 9:29

Deepak Pandey

1,35214 silver badges21 bronze badges

Comments

OverZealous · Accepted Answer · 2011-08-10 23:09:47Z

8

You may be able to manually sort by splitting the text up in case there is trailing numerals, like so:

SELECT * FROM sort_test
ORDER BY SUBSTRING(text FROM '^(.*?)( \\d+)?$'),
         COALESCE(SUBSTRING(text FROM ' (\\d+)$')::INTEGER, 0);

This will sort on column text, first by all characters optionally excluding an ending space followed by digits, then by those optional digits.

Worked well in my test.

Update fixed the string-only sorting with a simple coalesce (duh).

edited Aug 10, 2011 at 23:09

answered Aug 10, 2011 at 23:04

OverZealous

39.6k15 gold badges100 silver badges101 bronze badges

4 Comments

OverZealous Over a year ago

Why the downvote? It works, and solves the situation. It's not the best solution, but it doesn't involve changing the database structure. At least comment if you feel the need to downvote.

Ryan Culpepper Over a year ago

-1 for bizarre kluge that doesn't solve the real problem. (What about strings with multiple spaces and/or numbers?) See the comments above about collations.

OverZealous Over a year ago

I wouldn't say it's a bizarre kludge. It adds the ability to sort trailing numbers numerically, without requiring a specific version of PG. It handles trailing numbers very well, so works well for sequentially numbered folders. It handles multiple spaces, because it's only checking to ensure that there is at least one space before the trailing digits. If you tried it, you'd see it worked, instead of assuming.

DariusVE Over a year ago

Not working for texts like: "H1C11", because it order for the first number, not for the tailing

Tor Händevik · Accepted Answer · 2012-02-28 13:33:16Z

5

OverZealous answer helped me but didn't work if the string in the database begun with numbers followed by additional characters.

The following worked for me:

SELECT name
FROM folders
ORDER BY
COALESCE(SUBSTRING(name FROM '^(\\d+)')::INTEGER, 99999999),
SUBSTRING(name FROM '^\\d* *(.*?)( \\d+)?$'),
COALESCE(SUBSTRING(name FROM ' (\\d+)$')::INTEGER, 0),
name;

So this one:

Extracts the first number in the string, or uses 99999999.
Extracts the string that follows the possible first number.
Extracts a trailing number, or uses 0.

answered Feb 28, 2012 at 13:33

Tor Händevik

611 silver badge4 bronze badges

Comments

oupoup · Accepted Answer · 2017-10-17 16:33:27Z

3

A Vlk's answer above helped me a lot, but it sorted items only by the numeric part, which in my case came second. My data was like (desk 1, desk 2, desk 3 ...) a string part, a space and a numeric part. The syntax in A Vlk's answer returned the data sorted by the number, and at that it was the only answer from the above that did the trick. However when the string part was different, (eg desk 3, desk 4, table 1, desk 5...) table 1 would get first from desk 2. I fixed this using the syntax below:

    ...order by SUBSTRING(name,'\\w+'), SUBSTRINGname FROM '([0-9]+)')::BIGINT ASC;

edited Oct 17, 2017 at 16:33

answered Oct 17, 2017 at 15:16

oupoup

759 bronze badges

Comments

no1uknow · Accepted Answer · 2014-03-11 16:47:32Z

1

Tor's last SQL worked for me. However if you are calling this code from php you need add extra slashes.

SELECT name
FROM folders
ORDER BY
COALESCE(SUBSTRING(name FROM '^(\\\\d+)')::INTEGER, 99999999),
SUBSTRING(name FROM '^\\\\d* *(.*?)( \\\\d+)?$'),
COALESCE(SUBSTRING(name FROM ' (\\\\d+)$')::INTEGER, 0),
name;

answered Mar 11, 2014 at 16:47

no1uknow

5492 gold badges7 silver badges18 bronze badges

Collectives™ on Stack Overflow

Postgresql sorting mixed alphanumeric data

7 Answers 7

2 Comments

Comments

Comments

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

Comments

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related