Sort Alphanumeric Numbers

I have an SQL database that Crystal Reports 10 creates reports from.  It does not sort the Unit Numbers the way I want them to sort.  They are alphanumeric.  Crystal sorts like this:

10-D
100-B
20-F
231-C
40

I want to sort like this:

10-D
20-F
40
100-B
231-C

I found out how to do this in SQL and it is done like this:

ORDER BY CAST(LEFT(tblRenDet.RenDUnitNo, ISNULL(NULLIF(CHARINDEX('-', myfield) - 1, -1), "LEN(myfield))) AS INT), myfield

Does anybody know how to do this in Crystal Reports? Thanks.
molardAsked:
Who is Participating?
 
OutinConnect With a Mentor Commented:
Tss it must be monday..

should be

If {FIELD} like "*-*" then
tonumber(left({FIELD},InStr ({FIELD},"-")-1))
else tonumber({FIELD})

I assume there is only data like
10-A
10
but no data like
A
10A
etc.
0
 
bdreed35Commented:
Create a formula that returns the numeric value of the field:

//@Alpha Sort
val({table.field})

Sort on this formula rather than the actual field.
0
 
peter57rCommented:
Hi molard,
Create a formula field which gets the numeric value and sort on the formula field:

@Mysort:

val({mytable.myfield})

Pete
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
molardAuthor Commented:
It sorts the ones that are only numeric correctly but the alpha numeric ones it still sorts as if it were text instead of the way I would like. If I sort ascending, it sorts all the alpha ones as text and it has all of those together.  It then sorts all the numeric ones correctly.  For example half of my report has the alpha ones sorted at the top and the rest of the report has the numeric ones sorted.

I created a formula called AlphaSort that consists of:

val({mytable.myfield})

I chose to sort by the formula AlphaSort

Great suggestions from the both of you.  Can you think of something else I could try?
0
 
OutinCommented:
create a formula that does about the same:

tonumber(left({FIELD},InStr ({FIELD},"-")-1)

then order by this formula and then {FIELD}
0
 
OutinCommented:
and add a closing ) at the end of the formula :-)

hth,

Outin
0
 
bdreed35Commented:
How large can the number portion get?
This formula will work by adding leading zeroes to the number portion:

//@AlphaSort

totext(split({table.field},"-")[1],"00000") & "-" & split({table.field},"-")[2]
0
 
molardAuthor Commented:
Outin,

I tried that but I get an error that says:

String length is less than 0 or not an integer.

Thanks for the try.


bdreed35,

The number portion does not get higher than 900.  I will try your formula as well.

Thanks.
0
 
OutinCommented:
Before using my formula or the formula bdreed35 proposed, you should check on the presence of a "-" in the field, otherwise an error is generated:

If {FIELD} like "*-*" then
tonumber(left({FIELD},InStr ({FIELD},"-")-1))
else {FIELD}

--
Outin
0
 
OutinCommented:
Hmm thanks to slow network here you were quicker :-)

--
Outin
0
 
molardAuthor Commented:
You did it Outin!  Thanks!
0
 
OutinCommented:
Glad it worked.

Btw it's always nice to see how one problem can have different approaches to find a solution...

--
Outin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.