sponsored links

Collapse

Announcement

Collapse
No announcement yet.

Order By on Char field - numbers before letters?

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

  • Vectorspace
    started a topic Order By on Char field - numbers before letters?

    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)

  • Vectorspace
    replied
    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?

    Leave a comment:


  • TedHolt
    replied
    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.

    Leave a comment:


  • Rocky
    replied
    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.

    Leave a comment:


  • JonBoy
    replied
    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.

    Leave a comment:


  • Notinkeys
    replied
    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.

    Leave a comment:

sponsored links

Collapse

Working...
X