ORDER BY the last number of characters

elabuwa · 11-09-2011, 01:39 AM

Hi guys,

I have a table that stores an ID number in the below method. Student ID number to be exact.
student_id
09/09/PG-5/117
09/09/NR-5/120
09/09/G1-1/119
09/09/PG-6/118

Desired Result
09/09/PG-5/117
09/09/PG-6/118
09/09/G1-1/119
09/09/NR-5/120

I'd like to show them in the order of the last three numbers OR in the order of the numbers that appear after the last "/" character. Is there a way make my SQL statement to get the desired result, or do I have to record them in a temp table and pull records from that?

Cheers

Old Pedant · 11-09-2011, 02:10 AM

Sure. Easy.

For last 3 characters:

Code:

ORDER BY RIGHT(id,3)

For a *number* after the slash (and allowing the number to be any length but wanting them sorted *as* numbers), it's more complex but doable:

Code:

ORDER BY CONVERT( REVERSE( LEFT( REVERSE(id), INSTR( REVERSE(id), '/' )-1 ) ), DECIMAL )

I'll explain that one.

REVERSE(id) will take "09/09/PG-5/117" and convert it to "711/5-GP/90/90"

INSTR( "711/5-GP/90/90", '/' ) will return 4, the position of the first / in that string

LEFT( "711/5-GP/90/90", 4-1 ) thus gets us "711"

Which we REVESE again to get 117.

And we can then convert 117 to a number (decimal).

And now "09/09/XY-1/1137" *will* come out as larger than "09/09/AB-4/73" which it wouldn't if we just did ORDER BY the *strings* "1137" and "73".

You pays your money and takes your choice.

elabuwa · 11-09-2011, 02:56 AM

holly smokessss.
Thanks oldii.
Will check and let you know.