Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2009
    Posts
    37

    Database Internationalization (i18n) and Collation

    Hi guys

    Assuming that the solutions to my problem aren't technology specific I have posted this here.
    The issue that I am facing is that we are going to internationalize our application and we have a large amount of stored procedures/queries in or code. So within all of this logic there are queries with "ORDER BY" and a string column, which would order the results based on the collation e.g. alphabetical order for English. Assuming that there will be many more problems like this what is the best way to manage the requirements of needing multiple collations within databases. The only solution that I know of is by using a separate database for each language/locale, which I imagine could become very difficult to manage.
    If anyone knows some better solutions to this please let me know.

    Thanks
    Liam

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I will hold my hands up now and state that I have never had to worry about this problem. However, I think it is another example of why data order should be considered the job of the presentation layer and not the back end. Return unordered result sets to the front end and put your locale specific ordering logic there.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2009
    Posts
    37
    Yes this is very true. I suppose considering the amount of work that will be involved in a project like this redesigning the way these types of queries work is probably not going to be that much of a big deal.

    Thanks for the reply.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    This is definitely a presentation issue when dealing with small result sets (under 1000 rows).

    There are ways to deal with this at the database level when necessary for large result sets (such as paging issues), but that's a can of worms you're better off leaving unopened unless you really need to deal with it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Not something I have ever had to do either, but Oracle does allow you to set the sort collation sequence at the session level, so different users could potentially seet different sorts for the same data. See Oracle: Linguistic Sorting and String Searching if you want to know more.

    Example (based on the above link):
    Code:
    SQL> SELECT * FROM test3 ORDER BY name;
    
    NAME
    --------------------
    Diet
    Freizeit
     voir
    
    SQL> alter session set  nls_sort='german'
      2  /
    
    Session altered.
    
    SQL> SELECT * FROM test3 ORDER BY name;
    
    NAME
    --------------------
     voir
    Diet
    Freizeit

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •