Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-03-20 : 09:50:03
|
| Michael R writes "I am using SQL Server 2000 w/ Windows 200 SP4.I am attempting to write an application to display part numbers.These part numbers have corrosponding reference numbers such as "1a" and "23b".I am having trouble finding a working method for ORDER BY in order to display them properly.The column is named "ref", when I use "SELECT * from partlist ORDER BY ref" it works to a degree, however it is a varchar datatype so it sorts it with 1, 1a, 10, 11 .. 2, 20, etc.I would like some help on how I can correctly ORDER BY and display it.I have tried this as well (getting what I can from your articles):SELECT * FROM partlist WHERE (model = 'ABCD1000') ORDER BY CASE WHEN (ref != 'N/A') THEN CASE WHEN (LEN(ref) = 3) THEN CAST(SUBSTRING(ref, 1, 2) AS Integer) WHEN (LEN(ref) = 2) THEN CAST(SUBSTRING(ref, 1, 1) AS Integer) WHEN (LEN(ref) = 1) THEN CAST(ref AS Integer) END END ASCAs you can guess, our "accessories" are labelled as "N/A" concidering they did come come with the product.I apreciate the help and thank you in advance." |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-03-20 : 10:12:29
|
| What's wrong with that query?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Using Order By with Varchar (of undetermined lengths)