ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Order By on Char field - numbers before letters?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Order By on Char field - numbers before letters?

    When you order by a character field, letters are sorted before numbers.
    E.g.
    'AZA'
    'A9A'
    'AAA'
    'A0A'
    Sorts ascending to:
    'AAA'
    'AZA'
    'A0A'
    'A9A'

    Is there any way to make SQL sort numbers before letters? So I would get this instead:
    'A0A'
    'A9A'
    'AAA'
    'AZA'

    Note that I care about numerics anywhere in the field (which is a varchar 32)

  • #2
    Try this- First, CHGJOB SRTSEQ(*LANGIDSHR).Then change the sort sequence for your SQL to *JOBRUN. Note that you'll need to do this for each run of this job, and it will affect other components of your job as well.

    You could also create your own sort sequence, but that can be a real pain in the a... ummm... neck.

    Comment


    • #3
      Not sure about SQL but I know for RPG when you do this you have to be cautious because it may affect more than the sort sequence. For instance with RPG it also affects _all_ comparisons, and that includes lookups etc.

      Comment


      • #4
        If you're doing this as embedded SQL in RPG:

        At the start of your code you should have SET OPTION - one of the options is SRTSEQ = *LANGIDSHR and it will do what you want.

        Here is what I have:

        Code:
        EXEC SQL                              
          SET OPTION NAMING = *SYS,           
                     CLOSQLCSR = *ENDMOD,     
                     COMMIT = *NONE,          
                     SRTSEQ = *LANGIDSHR,     
                     DATFMT = *ISO;
        If you're doing it using STRSQL - do an F12 - 1 (Change session attributes) page down and change Sort Sequence to *LANGIDSHR

        If you're using a different means than above, look how to set the sort sequence for the connection.

        Comment


        • #5
          Originally posted by JonBoy View Post
          Not sure about SQL but I know for RPG when you do this you have to be cautious because it may affect more than the sort sequence. For instance with RPG it also affects _all_ comparisons, and that includes lookups etc.
          If you use *LANGIDUNQ instead of *LANGIDSHR, SQL will also sort digits ahead of letters, but uppercase letters and lowercase letters will not sort as equivalents.

          Comment


          • #6
            In the end I opted to do things in a completely different way, eliminating the need for this.
            However I noticed that if I ordered by the column cast to CCSID 1208 (UTF-8), that does sort numbers before letters. So I figured I could have done that and create an index for the same conversion. Would that have worked?

            Comment

            Working...
            X