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?
