DB2 sorting by lowercase uppercase – problem

db2sorting

I'm struggling with DB2 – sorting by Polish letters with diacritics and uppercase/lowercase. When I created a database using:

CREATE DATABASE P1DS8 AUTOMATIC STORAGE YES ON '/home/db2inst1' DBPATH ON '/home/db2inst1' USING CODESET UTF-8 TERRITORY PL COLLATE USING SYSTEM_912_PL PAGESIZE 32 K

Sorting by Polish letters works properly, however ascending sorting returns results ordered by lowercase as first aAbB… How I can change this setting to revert results to AaBb..?

Is the 'COLLATE' section is responsible for all aspects of sorting? (Specific language letters, lowercase, uppercase etc. )

Best Answer

You'd need to use the UCA (Unicode Collating Algorithm): the COLLATE value would be something like UCA500R1_LPO_CU for Polish and uppercase first. More info here.

Note that you cannot change the collating setting of an existing database; you would have to create a new one with the required collation and move your data.

Related Solutions

Postgresql – Postgres Client – save table sorting

Why not create a VIEW? The upside is that it is client independent.

Ms-access – Sorting Report entries on alphanumeric combination

Your tag says MS Access, if you are using Jet/ACE in MS Access, you can sort by a user defined function.

Very roughly

SQL

SELECT t.ID, t.SortHere, SortOrder([SortHere]) AS ToSort
FROM Table t
ORDER BY SortOrder([SortHere]);

Code

Function SortOrder(ToSort)

Part1 = Left(ToSort, 1)

For i = 2 To Len(ToSort)
    If Not IsNumeric(Mid(ToSort, i, 1)) Then
        Part2 = Part2 & Mid(ToSort, 2, i - 2)
        Part3 = Mid(ToSort, i, 1)
        Part4 = Mid(ToSort, i + 1)
        Exit For
    End If
Next

If Part2 = vbNullString Then
    Part2 = Mid(ToSort, 2)
End If

SortOrder = Part1 & Format(Part2, "0000") _
          & Part3 & Format(Part4, "0000")

End Function

I am sure you can come up with a better function with a little thought.

Best Answer

Related Solutions

Postgresql – Postgres Client – save table sorting

Ms-access – Sorting Report entries on alphanumeric combination

Related Question