How to sort strings with special characters in Hibernate

hibernatesorting

For example, if the Order in criteria is ascending and if we have two names like follows:

  • _zname_9
  • 1_aname_

I get an output (from hibernate result) like:

  • _zname_9
  • 1_aname_

But the expected output (java sorting) is:

  • 1_aname_
  • _zname_9

Am I missing something? Please note that I dont want to escape special characters while setting the criteria.

Can we mention regarding special characters while sorting?

Best Answer

I'm not an expert when it comes to collations but the following does provide the sorting your require. It may cause other characters to be sorted unexpectedly so please test.

WITH cte AS
(
    SELECT  '_zname_9' AS SortVal
    UNION ALL
    SELECT  '1_aname_' AS SortVal
)

SELECT  *

FROM    cte 

--ORDER BY  SortVal
ORDER BY SortVal COLLATE SQL_Latin1_General_Cp437_BIN2

I've used a BIN2 collation to cover that fact that your name column may be a unicode data type.

Related Question