Sorting on Foreign Language Characters, CR-XI and Oracle 2

[RobPimlott2]

I need to sort on a list of names created by users in Hungary and Poland (there may be others), where the characters have various accents. For example, sorting on last name, Timea ÁRMÓS is listed after Boglarka Zsiros. This is probably due to the method of sorting in Crystal. The preferred option (using this example) is to include "Á" with all the other "A"s.

The data source is Oracle, and my Oracle colleagues have drawn a blank too.

I'd welcome feedback on either Crystal or Oracle. Has anyone come across this and found a solution?

 

[Madawc]

I'd create a formula field. @SortName, which replaces accented characters by their unaccented equivalent.

 Replace ({surname}, "Á", "A")

With several types of accented characters, you might need to nest your commands, e.g.

 Replace (Replace ( {surname}, "Á", "A")), "Ó", "O")

You have the sorting problem because PC systems were designed around ASCII, which didn't allow for accents because the USA and UK don't normally use them. Crystal now uses Unicode, but I suppose the problem still remains. (It helps to give your Crystal version).

You can use Chr(@Variable) to print out a list of the ASCII posibilities, and what they print out of. Put in a detail line and increase @Variable by one each line.



Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[RobPimlott2]

Thanks, Madawc. I'll try the Replace function. It's going to mean a lot of searching for all the unicode characters that need changing - I use the function AscW() by the way. I'll use the formula itself as the sort key, as I still need to show the names in full, accents and all.

I'm using version XI (it's in the thread title), which won't be a lot different from version 10. Thanks again.

 

[IdoMillet]

The Replace approach would work, but keep in mind that you need to handle upper/lower case separately.

This functionality can also be addressed via a User Function Libraries (see list at:

http://www.kenhamady.com/bookmarks.html).

I recently added a ReplaceAccentedChars() function to my "CUT Light" UFL.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[RobPimlott2]

Thanks for this Ido. For now I'll create a formula with the Replace function, and ultimately I'll persuade someone here to buy your CUT Lite.

Rob Pimlott