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
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
Use -n in your sort command, so that it sorts numerically. Also -r allows you to reverse the result: $ sort test.txt | uniq -c | sort -n 1 ada 1 ccd 1 cd 1 nnaa 2 aaa 2 bbb 4 aa 10 naa $ sort test.txt | uniq -c | sort -nr 10 naa … 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
order by LEN(value), value Not perfect, but works well in a lot of cases.
readarray -d ” entries < <(printf ‘%s\0’ *.fas | sort -zV) for entry in “${entries[@]}”; do # do something with $entry done where printf ‘%s\0’ *.fas yields a NUL separated list of directory entries with the extension .fas, and sort -zV sorts them in natural order. Note that you need GNU sort installed in order … Read more
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!
Here is a quick solution: SELECT alphanumeric, integer FROM sorting_test ORDER BY LENGTH(alphanumeric), alphanumeric