4

I need to check the sorting in the table and table content is given by MySQL. I'm trying the following: Collections.sort(sorted, String.CASE_INSENSITIVE_ORDER);

And get the following result:
tes3@test.com
test4@test.com
test5@test.com
test@test.com
test_user@mail.com
user-og@driver.com

And this is what I get from MySQL by query:
SELECT 'email' FROM 'user' WHERE 1 ORDER BY 'user'.'email' ASC :

tes3@test.com
test_user@mail.com
test@test.com
test4@test.com
test5@test.com
user-og@driver.com

Seems like Java sorts according to ASCII table: http://www.asciitable.com/ 4 (52) - @ (64) - _ (95)

But in MySQL result the order is _ -> @ -> 4

The email field collation is: utf8_unicode_ci
What is the problem and is there any comparator to make ordering in the same way?

Bohdan Nesteruk
  • 794
  • 17
  • 42
  • AFAIK java handels the `_` different then they are getting handelt in sql when you are sorting. But the cause of this could be your local language setting. – SomeJavaGuy Nov 30 '15 at 13:38
  • how did you do it inside ur code? – JBALI Nov 30 '15 at 13:49
  • Possible duplicate of [MySQL 'Order By' - sorting alphanumeric correctly](http://stackoverflow.com/questions/8557172/mysql-order-by-sorting-alphanumeric-correctly) – JFPicard Nov 30 '15 at 14:13

1 Answers1

2

Use Collator:

The Collator class performs locale-sensitive String comparison. You use this class to build searching and sorting routines for natural language text.

And code will be:

Collator coll = Collator.getInstance(Locale.US);
coll.setStrength(Collator.IDENTICAL); 
Collections.sort(words, coll);
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
user987339
  • 10,519
  • 8
  • 40
  • 45
  • 2
    Thank you, this helped with symbols like digits and "@", but now I have problems with `space` recognizing. MySQL output: `Existing_test_company` `Test company` `Test company 10` `test_` `test_1` Java sorting result using Collator: `Existing_test_company` `test_` `test_1` `Test company` `Test company` – Bohdan Nesteruk Nov 30 '15 at 15:26
  • 2
    Solved by adding a rule: `String rules = ((RuleBasedCollator) Collator.getInstance(Locale.US)).getRules(); RuleBasedCollator correctedCollator = new RuleBasedCollator(rules.replaceAll("<'\u005f'", "<' '<'\u005f'"));` – Bohdan Nesteruk Nov 30 '15 at 15:41