Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ORDER BY question

Status
Not open for further replies.
Laeg

Laeg

Programmer
Nov 29, 2004
95
IE
MSSQL

I have a field called FileName of type varchar containing info like 123.doc, 124.doc, 125.doc and when I do an ORDER BY FileName it manages to order the files correctly. How is this so seeing as the field is a varchar?

Thanks,
Naoise
 
Sort by date Sort by votes
Assuming that all the file names are 3 digit numbers then sorting alphabetically will be the same as ordering those numbers numerically. However, if you have something like 1234.doc in there then you will get problems:

Code: 
123.doc
1234.doc
124.doc
125.doc

--James
 
Upvote 0 Downvote
Try this:

SELECT * FROM table
ORDER BY CAST(REPLACE(filename,'.doc','') AS integer)


(REPLACE is not ANSI/SQL, but still supported by many DBMS products since its specified by the ODBC API.)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sal21
  • Locked
  • Question
SORT by part of filed 2
Replies
5
Views
226
2009luca
2009luca
swhiteside
  • Locked
  • Question
Max Date By Training code - Crystal XI
Replies
3
Views
194
PHV
PHV
joycesolomon
  • Locked
  • Question
Order By Statement, help please
Replies
3
Views
124
joycesolomon
joycesolomon
JazzMaan
  • Locked
  • Question
Group by question
Replies
7
Views
158
JazzMaan
JazzMaan
soushi01
  • Locked
  • Question
Retrieve duplicate records by date time 2
Replies
3
Views
146
soushi01
soushi01

Part and Inventory Search

Sponsor

Back
Top