Question Re: Sorting of alphanumeric data in Technology Q&A

Sorting of alphanumeric data

Former Member — Fri, 13 Feb 2009 20:47:48 GMT

Hi,

I have data which contains letters, numbers, and some special characters. String length is not fixed. I want to sort it after certain number of characters.

e.g.

A-BC-111 these first characters/pattern is fixed but after that pattern and number of characters are not fixed.

A-BC-111-A105-13134A

A-BC-111-A001-1

A-BC-111-A002-15

A-BC-111-A001-10

A-BC-111-A001-11

A-BC-111-A001-12

A-BC-111-B001-2

A-BC-111-A001-3

A-BC-111-A001-2

A-BC-111-B001-1

The above records should be sorted in following order

A-BC-111-A001-1

A-BC-111-A001-2

A-BC-111-A001-3

A-BC-111-A001-10

A-BC-111-A001-11

A-BC-111-A001-12

A-BC-111-A002-15

A-BC-111-A105-13134A

A-BC-111-B001-1

A-BC-111-B001-2

I have number of different patterns. Sorting starts from different positions.

Any help will be appreciated.

Thanks.

Re: Sorting of alphanumeric data

Former Member — Fri, 13 Feb 2009 21:09:10 GMT

Simply add your field to a formula with the following:

Fieldname [10 to 20]

and sort by that field.

This will pull the 10th to 20th character. You can do more or less characters, but with the sample data you gave, 10 should be your starting point.

Chad

Edited by: Chad Lindell on Feb 13, 2009 10:09 PM

Re: Sorting of alphanumeric data

Former Member — Fri, 13 Feb 2009 21:24:53 GMT

Thanks for you prompt reply.

Sorry I tried it but didn't work. My code is working to some extent but last characters are not in order. Can you please write little more in detail?

Same report has number of patterns and in each pattren sorting starts from different positions. e.g. 10, 11, 16, 8

Edited by: Sushama Wankhade on Feb 17, 2009 9:13 PM

Re: Sorting of alphanumeric data

Former Member — Tue, 17 Feb 2009 21:44:00 GMT

Hi Sushama,

Looking at your desired sorting results, I think using the standard CR sorting feature will sort it exactly as you specified.

On the field that you are looking to sort assuming you have no groupings, try the following:

Click on Report tab at the top of your screen -> Record Sort Expert -> bring your report field across to your sort field -> for sort direction, click on ascending order. Again, this should work assuming you have no groupings in your report.

I hope this helps,

Regards,

Zack H

Re: Sorting of alphanumeric data

Former Member — Wed, 18 Feb 2009 14:22:10 GMT

Hi Zack,

Thanks for your reply.

As per standard CR sorting feature I don't get the sort the way my client wants. It comes like

A-BC-111-A105-8307

A-BC-111-A105-8341

A-BC-111-A105-8347

A-BC-111-A105-8420

A-BC-111-A6-B854

A-BC-111-A6-B3005

A-BC-111-A6-C801

A-BC-111-A6-C803

But actually he wants

(all A6 first then A105)

A-BC-111-A6-B854

A-BC-111-A6-B3005

A-BC-111-A6-C801

A-BC-111-A6-C803

A-BC-111-A105-8307

A-BC-111-A105-8341

A-BC-111-A105-8347

A-BC-111-A105-8420

And again by CR sorting last two digits come as

-1

-11

-12

-13

-2

-3

But we want

-1

-2

-3

-11

-12

-13

So I have to write code to check each character from sort position number whether it's a number or a letter and then use those formulas in sorting. But still didn't get the order of all patterns in the same report. One pattern worked but then second pattern as sort position is different not in order.

Thanks,

Sushama.

Re: Sorting of alphanumeric data

Former Member — Wed, 18 Feb 2009 14:24:54 GMT

Repeated reply so discarded it.

Edited by: Sushama Wankhade on Feb 18, 2009 4:06 PM

Re: Sorting of alphanumeric data

Former Member — Wed, 18 Feb 2009 14:24:54 GMT

repeated reply.

Edited by: Sushama Wankhade on Feb 18, 2009 4:07 PM

Re: Sorting of alphanumeric data

Former Member — Wed, 18 Feb 2009 14:25:33 GMT

My problem is solved by some other person.

Thanks for replies.

Edited by: Sushama Wankhade on Feb 19, 2009 6:09 PM

Re: Sorting of alphanumeric data

Former Member — Thu, 19 Feb 2009 17:12:38 GMT

Problem is solved by someone else and not from the replies I got on forum. But thanks for reply.

Re: Sorting of alphanumeric data

Former Member — Wed, 29 Apr 2009 00:44:54 GMT

Hi Sushama

I am hoping you can let me know how you got around this?

Thanks very much

duffey