Sorting a field with text and numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Yep you can
You can add an extra column to your query

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

- Raoul
 
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
was wondering HOW I could sort based on those LAST 5 numerical digits
Click to expand...

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

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


B Wishes


Tim F
 
Back
Top