SQL Collation to match IBMi

  • In SQL, these items sort this way

    INV175

    INV190

    INVB03

    INVB04

    On the IBM iSeries, they sort.

    INVB03

    INVB04

    INV175

    INV190

    Is there a SQL Collation I can use in a query to make a SQL Select ORDER BY match the IBM Sort ?

     

    EDIT: I found this in an old post, which seems to work: COLLATE SQL_EBCDIC037_CP1_CS_AS

     

     

    • This topic was modified 6 months, 1 week ago by  homebrew01.
    • This topic was modified 6 months, 1 week ago by  homebrew01.
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • You will need some form of EBCDIC collation to make SQL sort in the same way as IBM.

    The standard collations used by Windows, Linux, and SQL Server have numbers before letters, see the ANSI spec.  Most IBM machines use the EBCDIC spec, which has letters before numbers.

    If your data is currently held in ANSI format you will have to reload all of it to get it into EBCDIC format.  This is not a trivial exercise and you should research this fully before attempting it.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply