1

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?

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.