Sorting a field with text and numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MacNut - 10 Mar 2005 15:59 GMT

I have a field with information in this type of format: NB-TS-WP-46048 and i
was wondering HOW I could sort based on those LAST 5 numerical digits. Right
now, it's sorting in ascending order based on the first 2 characters.

Is this possible?

Thanks

Reply to this Message

JaRa - 10 Mar 2005 16:17 GMT

Yep you can
You can add an extra column to your query

e.g. SELECT Column FROM Table ORDER BY Right(Column,5)

- Raoul

> I have a field with information in this type of format: NB-TS-WP-46048 and i
> was wondering HOW I could sort based on those LAST 5 numerical digits. Right
[quoted text clipped - 3 lines]
>
> Thanks

Reply to this Message

MacNut - 10 Mar 2005 16:35 GMT

Perfect. Thanks so much!!
MN

> Yep you can
> You can add an extra column to your query
[quoted text clipped - 10 lines]
> >
> > Thanks

Reply to this Message

John Nurick - 10 Mar 2005 16:23 GMT

Add a calculated field to your query, like this:

SortOrder: Right([XXX], 5)

where XXX is the field name. Then sort on the calculated field.

If you have a large number of records this will be quite slow. Consider
separating the last five digits into a field of their own and indexing it.
If the different parts of your field have their own meanings there's a
strong argument for splitting them into separate fields anyway.

>I have a field with information in this type of format: NB-TS-WP-46048 and
>i
[quoted text clipped - 5 lines]
>
> Thanks

Reply to this Message

Tim Ferguson - 10 Mar 2005 18:52 GMT

> i
> was wondering HOW I could sort based on those LAST 5 numerical digits

SELECT Whatever
FROM Wherever
ORDER BY RIGHT(MyField, 5) ASC

You might also think about normalising the table design...

B Wishes

Tim F

Reply to this Message