locked
sql sort order national RRS feed

  • General discussion

  • VB.Net Visual Basic

    Sort order.

    In sql I would like to sort according to the Swedish alphabet.
    How to do that?

    The default order is the English alphabet.

    I have not found any discussions or answer to this issue.

    (Maybe I have not been able to find them?)


    Appleforce

    Sunday, July 23, 2017 7:51 AM

All replies

  • In case of SQL queries, consider the COLLATE clause: https://docs.microsoft.com/en-us/sql/t-sql/statements/windows-collation-name-transact-sql

    Example:

       SELECT *
       FROM MyTable
       ORDER BY MyColumn COLLATE Finnish_Swedish_CI_AI

    Sunday, July 23, 2017 8:54 AM
  • What is the difference between the Swedish and the English Alphabet in sort order. 

    Although the official alphabet for all West European languages  has sometimes more or less characters. 

    (The Swedish one 29, English 26 and Italian only 21)

    Is the sort order for all West European languages for all the characters the same on computers with a Windows OS.

    (Swedish, English, Norwegian, German, Luxembourgish, French, Dutch, Italian, Danish, Spanish, and some more.) 

    Be aware that in many of those languages characters like currency sights are used and more of those for instance accents.

    However, be aware this is a VB forum and therefore your question is complete Off Topic


    Success
    Cor




    Sunday, July 23, 2017 9:24 AM
  • Thanks for answering!

    I cannot make this work though, in the context of Visual Basic and Access databases.
    Maybe it is working in some other context, maybe I do not know the correct names of the constants (Finnish_Swedish_CI_AI etc) in this context, I do not know.


    Appleforce

    Friday, July 28, 2017 5:53 AM
  • Thank you!

    You do not know what you are talking about, apparently, I think perhaps you are out of topic.


    Appleforce

    Friday, July 28, 2017 5:55 AM
  • Seems that in Access you can create a VBA function, which converts the strings to a sequence of two-digit hexadecimal codes, so that the sorting by this string will give the desired order:

    Write a simplest one (in Access) and check if it can be used from your queries performed in VB.NET, such as “SELECT * FROM MyTable ORDER BY StrToHex(MyColumn)”.

    If it works, then adjust the function according to your needs. For Latin letters, use the code from the sample. For specific letters, assign two-digit hexadecimal codes that will give the desired order. For example, maybe use “80” for å, “81” for ä,  “82” for ö. If case-sensitivity is not needed, then maybe convert the string to lowercase using LCase before making hexadecimals.

    Friday, July 28, 2017 7:08 AM
  • Hello,

    When indicating Visual Basic are you saying VB.NET or Classic VB e.g. VB6 or VBA inside of MS-Access?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, July 28, 2017 9:03 AM
  • Hi Karen and Viorel. 

    Have you any documentation about this. Swedish is like the language of Holland (Originally Frisian) one of the so called Nordic languages. 

    It is the base of English. Or do you think the OP is somewhere from a country where not a West European language is used. 

    https://en.wikipedia.org/wiki/English_language

    Than the answer is simple. Use the English one. 


    Success
    Cor


    Friday, July 28, 2017 11:38 AM
  • VB.Net


    Appleforce

    Friday, July 28, 2017 1:40 PM
  • Then show the code about VB.Net. 

    In VB.Net it is with Swedish as all the replies I've given in this thread so tell also why you ask this as it is now you try for me to create a troll thread. 

    But somehow it seems impossible to get this thread moderated (change to question or move it to Off Topic)


    Success
    Cor


    Friday, July 28, 2017 1:45 PM
  • VB.Net Visual Basic

    Sort order.

    In sql I would like to sort according to the Swedish alphabet.
    How to do that?

    The default order is the English alphabet.

    I have not found any discussions or answer to this issue.

    (Maybe I have not been able to find them?)


    Appleforce

    I have found the problem and the exact same question posted in other fora. So I am not alone.
    Some think this is a bug in VB.Net but I think that the suggested solution via COLLATE just does not work in VB.Net.

    As we here are talking of a long-term project, actually started with the very first version of VB in 1992, via VB3 and the arrival of Access in 1995, via VB6 to VB.Net, maybe trouble has arisen on the way.

    What has been done to get around the problem is to create new databases in VB.Net and import the old long-term data. And what do you know, problem solved.

    Maybe this will help other frustrated alphabetic sorters.
     



    Appleforce

    Saturday, August 12, 2017 1:18 PM

  • I have found the problem and the exact same question posted in other fora. So I am not alone.
    Some think this is a bug in VB.Net but I think that the suggested solution via COLLATE just does not work in VB.Net.

     



    Appleforce

    Don't you think it is a bug in your head that you cannot find the answer and are you only trolling here.

    Your asking something the same as "How  can I calculate the 6 sides of the  box which is the world."

    Simply show somewhere what is in your idea the Swedish Sort order and than there can be an answer. Until now it is just Latin1 as it is called too or 1252 in Windows.

    https://sv.wikipedia.org/wiki/ISO/IEC_8859-1


    Success
    Cor


    Saturday, August 12, 2017 1:52 PM
  • Cor !

    Thanks for all your help, you are really insightful, where do you get it all?


    Appleforce

    Tuesday, August 15, 2017 1:44 PM