CODEWITHSUNDEEP
sql-serversql-order-byhana
Thorsten Niehues
Thorsten Niehues

Reputation: 14472

Sort varchar SQL Server vs HANA

I am about to compare datasets between SQL Server and SAP HANA using DbUnit.

In order to compare it is required that both datasets are sorted exaclty the same.

Unfortunately the sort order is different when there are alphanumeric characters are mixed with punctuation characters.

08:36:50.463 [main] ERROR com.meag.h4q.tests.DirtyValue - Diff found in row:181, col:UniqueID expected:EBEL_RE_EUR_UL, actual:EBEL_RE_EUR3.50
08:36:50.463 [main] ERROR com.meag.h4q.tests.DirtyValue - Diff found in row:182, col:UniqueID expected:EBEL_RE_EUR3.50, actual:EBEL_RE_EUR4.75
08:36:50.463 [main] ERROR com.meag.h4q.tests.DirtyValue - Diff found in row:183, col:UniqueID expected:EBEL_RE_EUR4.75, actual:EBEL_RE_EUR_UL
08:36:50.464 [main] ERROR com.meag.h4q.tests.DirtyValue - Diff found in row:663, col:UniqueID expected:BK_K_LAMBDG_EUR, actual:BK_K_LAMBDGV_EUR
08:36:50.464 [main] ERROR com.meag.h4q.tests.DirtyValue - Diff found in row:664, col:UniqueID expected:BK_K_LAMBDG_USD, actual:BK_K_LAMBDGV_GBP
08:36:50.465 [main] ERROR com.meag.h4q.tests.DirtyValue - Diff found in row:665, col:UniqueID expected:BK_K_LAMBDGV_EUR, actual:BK_K_LAMBDGV_USD
08:36:50.465 [main] ERROR com.meag.h4q.tests.DirtyValue - Diff found in row:666, col:UniqueID expected:BK_K_LAMBDGV_GBP, actual:BK_K_LAMBDG_EUR
08:36:50.465 [main] ERROR com.meag.h4q.tests.DirtyValue - Diff found in row:667, col:UniqueID expected:BK_K_LAMBDGV_USD, actual:BK_K_LAMBDG_USD

So far I did read that it depends on the collation used by the database. In SQL Server I did find the used collation: by viewing the table definition.

But what collation is used by SAP HANA so that I can match the sort order?

Upvotes: 0

Views: 245

Answers (1)

Thorsten Niehues
Thorsten Niehues

Reputation: 14472

Add 

COLLATE Latin1_General_bin

after the column to do binary search in SQL Server.

The order will be the same as in SAP HANA

Upvotes: 1

Related Questions