Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgresql sorting mixed alphanumeric data

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?

like image 442
el_quick Avatar asked Aug 10 '11 22:08

el_quick


4 Answers

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)
like image 166
Grzegorz Szpetkowski Avatar answered Oct 24 '22 19:10

Grzegorz Szpetkowski


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
like image 24
A Vlk Avatar answered Oct 24 '22 18:10

A Vlk


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
like image 32
Deepak Pandey Avatar answered Oct 24 '22 20:10

Deepak Pandey


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

like image 40
OverZealous Avatar answered Oct 24 '22 20:10

OverZealous