0

I can not find information on how to sort by name if it contains special characters.

Database stores Željko Arambašić name as Željko ArambaÅ¡ić (latin1_swedish_ci) and when sorted by name it comes of first because of starting Å in DB, but is of course presented right in UTF-8, just in wrong order place.

I found solution of MySQL that uses COLLATE inside query but my MariaDB is reporting an error on that part.

I can not find anywhere a replacement for that. Please advise.

SELECT * FROM STRANKE ORDER BY str_naziv COLLATE xxx;
ikiK
  • 6,328
  • 4
  • 20
  • 40
  • Why aren't you storing your strings with a character set that can encode it, like utf8mb4? That's the default character set in the current version of MySQL. It's hard to justify using anything else these days, especially if you need to support international characters. – Bill Karwin Feb 11 '21 at 17:24
  • Because shared host server is set like that and in time of building application year ago I didn't know how to change it. I just now noticed this problem. Everything else works. – ikiK Feb 11 '21 at 17:31
  • *Database stores Željko Arambašić name as Željko ArambaÅ¡ić (latin1_swedish_ci)* Do you want to say that your data is **already damaged**? If so then thinking about sorting makes no sense. – Akina Feb 11 '21 at 18:28
  • Indeed, if you have stored strings into a table that has no way of representing those encodings, your strings might be corrupted now. That is, there may be no way to recover the original characters unambiguously. – Bill Karwin Feb 11 '21 at 18:49
  • You may know now what you didn't know a year ago when you were developing the app: you can (and should) define explicitly the character set for a table or a column. You don't have to use the server-wide default. – Bill Karwin Feb 11 '21 at 18:53
  • @BillKarwin I know yes, but it is still pain in the *** all that about encodings. BTW I do know how to dump the base, re-open it with correct encoding with editor like Sublime, and I get all charters right, on my local testing computer base is default UTF, And i managed to transfer it. BTW I do have `COLLATE utf8_general_ci;` and `CHARSET=utf8` set on creation of my data tables and it still does not make any deference, I tried other things too. I just gave up on that one and used it as it is. This is only problem I have so far, I can tho sort it front-ed... – ikiK Feb 12 '21 at 10:04

0 Answers0