How to sort strings with special characters in Hibernate? 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?
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.