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?
Announcement
Collapse
No announcement yet.
Order By on Char field - numbers before letters?
Collapse
X
-
Originally posted by JonBoy View PostNot 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:
-
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 using a different means than above, look how to set the sort sequence for the connection.
- Likes 1
Leave a comment:
-
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:
-
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:
-
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)Tags: None
Leave a comment: