• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

Arrange number ID's in sort

Experts,

I have a crystal report that I am sorting the Field_ID in ascending order, but it sorts it not like 1 thru 50.  It ends up looking like this.
S1_d
S10_d
S11_d

It should look like this.
S1_d
S2_d
S3_d

I know crystal Reports must have a way of doing this correctly.  If not,  I will have to make a new column in the database's table and name it ID#. The add alphabets to sort.  

ID#     FIELD_ID
A                   S1_d
B                   S2_d
C                   S3_d

Any thoughts?
   
USING CRYSTAL REPORTS 2008 and Oracle 10 G          

0
Maliki Hassani
Asked:
Maliki Hassani
1 Solution
 
käµfm³d 👽Commented:
That's because your field is being sorted "alphabetically," and as such, "10" comes before "2". I can't test right now because I don't have access to Oracle, but you could try modifying your ORDER BY to something like:
ORDER BY CAST(REGEXP_REPLACE(Field_ID, '[^[:digit:]]', '') AS INTEGER)

Open in new window

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now