Skip to Content

ORDER_BY with collation in SAP Hana

In my table I've got values with polish diacritics signs. I want to query it and sort the result with the right order. I need collation to do this, but I don't know how to use it in SAP Hana database.

Test table

CREATE COLUMN TABLE TEST_ORDER_BY (
    ID BIGINT null,
    PL_VALUE VARCHAR (20) null,
    DE_VALUE VARCHAR (20) null 
);

INSERT INTO TEST_ORDER_BY VALUES(1,'Aaa','Straße');
INSERT INTO TEST_ORDER_BY VALUES(2,'aaa','Strasse');
INSERT INTO TEST_ORDER_BY VALUES(3,'Bbbb','Strase');
INSERT INTO TEST_ORDER_BY VALUES(4,'bbbb','Strasze');
INSERT INTO TEST_ORDER_BY VALUES(5,'Ąaa','Aaa');
INSERT INTO TEST_ORDER_BY VALUES(6,'ąaa','ßStrae');
INSERT INTO TEST_ORDER_BY VALUES(7,'zz','Zzzz');
INSERT INTO TEST_ORDER_BY VALUES(8,'zaąa','aaa');
INSERT INTO TEST_ORDER_BY VALUES(9,'zaąz','bbb');
INSERT INTO TEST_ORDER_BY VALUES(10,'zabz','Strasße');

SQL Query

SELECT id, pl_value
FROM TEST_ORDER_BY
ORDER BY pl_value ASC;

Result

ID  PL_VALUE
1   Aaa
3   Bbbb
2   aaa
4   bbbb
10  zabz
8   zaąa
9   zaąz
7   zz
5   Ąaa
6   ąaa

Expected result

        ID PL_VALUE            
---------- --------------------
         1 Aaa                 
         2 aaa                 
         5 Ąaa                 
         6 ąaa                 
         3 Bbbb                
         4 bbbb                
         8 zaąa                
         9 zaąz                
        10 zabz                
         7 zz                 

What I need is a to be followed by ą and same for other diacritics signs like ęóśłżźń. I found the view M_COLLATIONS in the database (without polish collation) and column COLLATION in TABLE_COLUMNS view, but I don't know how to set it for speciefied column.

What can I do? I use 2.00.030.00.1522210459 version (2.0 SPS 03) and if collation is not supported what is the workaround?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Mar 17 at 05:11 PM

    Cześć, Wojciech.

    Please try solution from Note 2448829

    For me the query

    SELECT pl_value
    FROM TEST_ORDER_BY
    ORDER BY pl_value ASC with parameters('LOCALE' = 'pl');

    returns the following result on 2.0 SPS 3:

    Aaa
    aaa
    Ąaa
    ąaa
    Bbbb
    bbbb
    zaąa
    zaąz
    zabz
    zz

    Regards,

    -Vitaliy

    Add comment
    10|10000 characters needed characters exceeded

    • This additional answer is one of the rare examples when resurrecting old questions adds benefit. Thanks, Vitaly!

      What's curious though is that the note is from 2017 and it states that the support for national sorting orders is a planned feature. As this note is literally the only place where this function is mentioned, this feature still seems to be in development. Might be risky to rely on functionality that is not documented and consistently implemented. (I assume that the whole WITH PARAMETER workaround just doesn't really help when one tries to migrate from platforms that handle locale specifics via session context.)

  • Aug 28, 2018 at 02:00 AM

    As answered on stackoverflow:

    Collations are not (yet?) supported with SAP HANA. I guess that this feature is in some state of preparation given the system views.

    Besides client side ordering, I don't see a good option to imitate collation based ordering in SQL/SQLScript.

    Add comment
    10|10000 characters needed characters exceeded