Ordering by part of a text string 2

[Sashanan]

In my SQL Server 7 database, one of the tables has a column containing records such as:

31S00220
68S00221
11S00222
48T00223

What I want to do here is order the reocrds by the last five characters in the string (as I've done in the example above). I assume the query will be a variation on the usual 'SELECT Column1,Column2 FROM Table ORDER BY Column1', but how would I change the ORDER part to have it look at the last five characters? Do I have any options for this in SQL?
"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[ThomVF]

order by substr(col,len(col)-4,5)

This will order by the last 5 characters of the string.
If you convinced that the last 5 characters will ALWAYS be numeric and you want to order numerically, use this:

order by cast(substr(col,len(col)-4,5) as integer)


Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[Sashanan]

Mmm...this throws the error "'substr' is not a recognized function name." Should I use an alternative for SQL Server 7?
"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[karluk]

The function is "substring" in SQL Server. Try

order by substring(col,len(col)-4,5)

 

[ThomVF]

Ooops - ORACLE syntax...

use "SUBSTRING" function

Sorry about the confusion.
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[Sashanan]

No problem. Works great now, thanks to you both! "Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)