Crystal Reports: Sorting String Fields Numerically

Generally speaking, if you need to sort by a field in a numerical fashion, it is obviously best to have the value of the field be a number. But, I have had a few situations where I needed to store the value of the field as a string but sort it in a numerical fashion (for example, fields where letters are allowed but generally are all numbers). If you do a generic string sort on a set of numbers, it does not come out in numerical order due to the fact that lengths are not taken into account. Anything that starts with a 0 comes first, then with a 1 next, etc. So, the following strings ‘200’, ’10’, ‘3020’, ‘420’, ’11’, ‘8’ get sorted as follows:


Obviously, not the desired result. On the other hand, if we prepend zeros to each of the strings like so, ‘0200’, ‘0010’, ‘3020’, ‘0420’, ‘0011’, ‘0008’, then the strings will be sorted in numeric order.


So, all you need to do is pad your string field with zeros so that each string is the same length. To do this in Crystal Reports, create a forumla field with the following code.

Right("000000000000000" & {Field},15)

The length of the string of zeros should be as long as the maximum length of the field and the second parameter of the Right function is as well the maximum length of the field. Then sort by this forumal field as opposed to the original field.

A simple solution that for some reason wasn’t completely apparent to me at first.


7 Responses to “Crystal Reports: Sorting String Fields Numerically”

  1. Ceetar Says:

    Nice tip, I’ve been pulling my hair out trying to do this, because I’ve got a complex formula returning the sort fields, and just recently added one that is numeric. Hoever, this doesn’t work if there are negative numbers.

  2. data031 Says:

    What happens to the alpha characters in the string? I have an alphanumeric string field that is broken up into 5 “sort” alphanumeric fields. So I actually have 6 fields for the number. For example, the “display” field may be 1992.A.23.1.a-c. The “sort” fields for this number are
    Sort1 = 1992
    Sort2 = A
    Sort3 = 23
    Sort4 = 1
    Sort5 = a-c

    Compared to the number of total records with these alphanumeric fields in my database, I have a “relatively” low number of alpha characters, but I still need to sort this number sequentially. Is there a formula to handle the alpha characters or will sorting not work as long as there is alpha characters in the number?

    Thank you!

  3. Lisa Holt Says:

    Hi: Does anyone know why a the data from a simple string field will not display or print when it has been inserted in a section of other simple string fields that do display. I created a rather complex Crystal report with many subreports and design refinements, but when asked by the user to add this string field none of the data is being returned even though I know the data is in the database record. Don’t know if this is enough information to go on, but I just can’t figure out this problem that shouldn’t be a problems. Thanks so much for any help you can give me.

  4. Rafal Says:


    what you can do it this case is to add large number to your numeric field so that none of your numbers are negative and then apply the solution explained in the artice

  5. Rafal Says:

    Lisa, maybe it is something to do with the links between your tables? When I have this kind of a problem I usually have to make sure that correct links are set as outer links etc.

  6. Victoria Rose Says:

    “Then sort by this forumal field as opposed to the original field.” Any idea how I do that? I used your first suggestion and that worked, but they are still out of order and I’m searching for the sort option.

  7. Reese Says:

    Is there a way I can use this formula but then hide the extra zeroes to the left?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: