custom sort order

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

Guest

Hi folks,
I have the following data that I need to sort numerically by the first field
(to the first semicolon) although some of the date it is alphanumeric:

3904;CHOW KIT, KNIFE FORK SPOON SILVER;1.8
106701;RANK, BDU TAB, 2LT;2.19
200UX15CR;FILM, FAX SHARP UX15CR (PREMIER);21.73
4413426;PATCH, CIVILIAN IN FIELD DCU;2.49
1433BRXL;SHIRT, SS TEE/LOOSE FIT, BROWN, XL;15.4
3ZV12;CABINET, FIRE EXTINGUISHER 30"H X 12"W X 9"D HPC F;65.04
4016;LINER, PONCHO DCU;39


I need the data in the following order without adding another field to sort
by. This is a small representation of 4000 plus records that are constantly
being modified.

3904;CHOW KIT, KNIFE FORK SPOON SILVER;1.8
4016;LINER, PONCHO DCU;39
3ZV12;CABINET, FIRE EXTINGUISHER 30"H X 12"W X 9"D HPC F;65.04
106701;RANK, BDU TAB, 2LT;2.19
4413426;PATCH, CIVILIAN IN FIELD DCU;2.49
1433BRXL;SHIRT, SS TEE/LOOSE FIT, BROWN, XL;15.4
200UX15CR;FILM, FAX SHARP UX15CR (PREMIER);21.73

Notice that the sorted list starts with four character entries, then five
character entries, then six character entries,.......

I also need the data in the first field expanded to 20 characters, with the
left side holding blank spaces as cell padding. I've figured out how to do
this with VB. I suppose the next step is to add a bubble sort routine to
order the records properly.

Any ideas information or insight would be greatly appreciated
Scott
 
You could try the following query (Provided if the longest ItemID is 20
character long):

SELECT ItemID, Item, Price
FROM Table1
ORDER BY LEFT('00000000000000000000', 20-LEN(ItemID)) + ItemID;

Lucas
 
Thanks a billion. Seems to be working like a charm except, for some reason,
it skips the top record.
Thanks
 
Everything seems to be working except those records that have leading zeros.
For instance, a record with the following first field "0078944:" will come
before the following "79145;"

Does this have to do with the zero string '00000000000000000000', in the SQL
statement?
Thanks
 
The zero string would cause that. If you want 79145 to come first before
0078944, then you can modify the query a bit to:

SELECT ItemID, Item, Price
FROM Table1
ORDER BY LEFT(' ', 20-LEN(ItemID)) + ItemID;

You can replace the 0's with spaces. This should work (provided that there
is no ItemID with leading spaces.

Lucas
 
Nothing important. Just wanted to mention that the
equivalent expression is a little simpler:
RIGHT('00000000000000000000' + ItemID, 20)
 
Back
Top