![]() You could do that with plpgsql, or kludge a regexp: select regexp_replace( If you don't want leading zeros in your labels, then you need to make a function that will add them on the fly. If you want to order them alphabetically, then a quick solution is to do it like ISO dates: 'Disk 01' indeed comes before 'Disk 10' because alphabetically '0' < '1'. I want and expect: DISK 1, DISK 2, DISK 3, DISK 10 With quotes meaning 'this is a text literal', '10' < '2' indeed. Numbers are letters too, and they sort alphabetically. This is not what you want, but it is the correct order when sorting text. However, it is built into ICU, and more and more systems are integrating ICU. ** To be fair, ICU / Unicode isn't "required" for doing this type of sorting, given that any collation or system can implement the same algorithm. different non-digit characters are handled as expected.multiple / separate groups of numbers within a string are handled separately.To more fully demonstrate how the "numeric" collation option works, I added some data to the previous example to show: You could even mention that the -kn-true part enables "numeric" sorting. Place a comment just after each query that uses this custom collation, pointing out that it is a custom collation, and include a link to the official documentation:.Prefix the name of the new collation with "custom_" to increase awareness that this is indeed a custom collation that might have non-obvious behavior and might not exist on other systems (hence might need to be added to a system or application setup process).Here is a working example of it:ĭue to collations not being generally well-understood, and the ability to customize them being more recent (for databases, at least) and even more esoteric, I would recommend doing the following when implementing this solution: ![]() You would need to create a custom collation (this one even comes straight from their documentation): CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true') Īnd use that in the ORDER BY. This is a necessary evil in many cases, but fortunately, PostgreSQL (at least as of version 10), does allow for this internally. Handling natural sorting is often done programmatically by chopping the string into its pure alpha and numerical pieces and then sorting on those individually. This option is not well known and not even available in most places, but any system that implements ICU (International Components for Unicode) ** has the potential for allowing this type of sorting (as long as it allows for customizing the sort options), which is often referred to as "natural" sorting. The most efficient way is to have the collation itself handle this internally. Sorting a "2" stored in a string type before the "15" can be done in a few ways. Sorting strings naturally puts "15" before "2" because the first digit in the "15" is a "1", which sorts before "2".
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |