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() … Read more
This is called a “natural sort order”, and is usually employed to sort items like those you have, like filenames and such. Here’s a naive (in the sense that there are probably plenty of unicode-problems with it) implementation that seems to do the trick: You can copy the code below into LINQPad to execute it … Read more
This is now possible in modern browsers using localeCompare. By passing the numeric: true option, it will smartly recognize numbers. You can do case-insensitive using sensitivity: ‘base’. It was tested in Chrome, Firefox, and Internet Explorer 11. Here’s an example. It returns 1, meaning 10 goes after 2: ’10’.localeCompare(‘2’, undefined, {numeric: true, sensitivity: ‘base’}) For performance when … Read more
Since Postgres 9.6, it is possible to specify a collation which will sort columns with numbers naturally. https://www.postgresql.org/docs/10/collation.html — First create a collation with numeric sorting CREATE COLLATION numeric (provider = icu, locale=”en@colNumeric=yes”); — Alter table to use the collation ALTER TABLE “employees” ALTER COLUMN “em_code” type TEXT COLLATE numeric; Now just query as you … Read more
Building on your test data, but this works with arbitrary data. This works with any number of elements in the string. Register a composite type made up of one text and one integer value once per database. I call it ai: CREATE TYPE ai AS (a text, i int); The trick is to form an … Read more
Google: Python natural sorting. Result 1: The page you linked to. But don’t stop there! Result 2: Jeff Atwood’s blog that explains how to do it properly. Result 3: An answer I posted based on Jeff Atwood’s blog. Here’s the code from that answer: import re def natural_sort(l): convert = lambda text: int(text) if text.isdigit() … Read more
Here’s how you can get explorer-like behaviour in Python: #!/usr/bin/env python “”” >>> items = u’a1 a003 b2 a2 a10 1 10 20 2 c100′.split() >>> items.sort(explorer_cmp) >>> for s in items: … print s, 1 2 10 20 a1 a2 a003 a10 b2 c100 >>> items.sort(key=natural_key, reverse=True) >>> for s in items: … print … Read more
I don’t think “alphanumeric sort” means what you think it means. In any case, looks like you want mixedsort, part of gtools. > install.packages(‘gtools’) […] > require(‘gtools’) Loading required package: gtools > n [1] “abc21” “abc2” “abc1” “abc01” “abc4” “abc201” “1b” “1a” > mixedsort(n) [1] “1a” “1b” “abc1” “abc01” “abc2” “abc4” “abc21” “abc201”
order by LEN(value), value Not perfect, but works well in a lot of cases.
In java the “natural” order meaning is “lexicographical” order, so there is no implementation in the core like the one you’re looking for. There are open source implementations. Here’s one: NaturalOrderComparator.java Make sure you read the: Cougaar Open Source License I hope this helps!