A database table that I've had to work with has a something called identifiers which are humanreable names of questions. There's a HTML template where the question designer can place the various questions in a human-sorted order. All questions get identifiers in the form of <something><number>
like this: Head1
or Soma3
or Reg10
where Head
, Soma
and Reg
are sections. Changing the nameing convention from Head1
to Head01
is too late because all the templates already expect Head1
not Head01
.
Initially I sorted the identifiers like this:
SELECT id, identifier
FROM questions
WHERE section=123
ORDER BY identifier
The result you would get is:
Head1
Head10
Head2
Head3
...
The human readable sort order should have been this:
Head1
Head2
Head3
...
Head10
To solve this now all I needed to do was to extract the two digit part and cast it as an integer. Like this:
SELECT id, identifier,
SUBSTRING(identifier FROM '[0-9]{1,2}')::INT AS identifier_int
FROM questions
WHERE section=123
ORDER BY identifier_int, identifier
The reason I who a second order by key is because some identifiers look like this:
Head9a
Head9c
Head9b
Comments
First time it works.
A second time. It will then work!
This time I can get it wrong if I want to.
No need to put it in the results:
SELECT id, identifier,
FROM questions
WHERE section=123
ORDER BY SUBSTRING(identifier FROM '[0-9]{1,2}')::INT, identifier
CREATE OR REPLACE FUNCTION human_sort(text)
RETURNS text[] AS
$BODY$
/* Split the input text into contiguous chunks where no numbers appear,
and contiguous chunks of only numbers. For the numbers, add leading
zeros to 20 digits, so we can use one text array, but sort the
numbers as if they were big integers.
For example, human_sort('Run 12 Miles') gives
{'Run ', '00000000000000000012', ' Miles'}
*/
select array_agg(
case
when a.match_array[1]::text is not null
then a.match_array[1]::text
else lpad(a.match_array[2]::text, 20::int, '0'::text)::text
end::text)
from (
select regexp_matches(
case when $1 = '' then null else $1 end, E'(\\D+)|(\\d+)', 'g'
) AS match_array
) AS a
$BODY$
LANGUAGE sql IMMUTABLE;
SELECT *
FROM "questions"
ORDER BY human_sort("identifier")
https://stackoverflow.com/questions/12965463/humanized-or-natural-number-sorting-of-mixed-word-and-number-strings