John Mitchell-245523 (7/25/2013)
That'll be because "A" comes after "-" in SQL_Latin1_General_Cp1251_CS_AS and all the other collations you've tried. Here's a few options I can think of:(1) Find a collation that has "-" after all the alphanumeric characters
(2) Change your database design so that you store the separate parts of your codes in separate columns
(3) Use a string splitter (search this site to find one) to split your codes into their separate parts before you do your ORDER BY
(4) Use REPLACE to replace "-" with some other character that comes after all alphanumeric characters in the collation and that does not appear in any of the codes. Replace that character with "-" again once you've done your sorting.
(5) Use a more generalised form of the query to make sure you return all the data you need and then have your presentation layer do the customised sorting and filtering
John
Edit: added option 5
Thank you so much for your fast reply.
Certainly, option 2 is not possible because it may affect system design. I had found a similar example in this forum as your option 4 as below but result is the same,
SELECT part_code,
REPLACE(REPLACE(part_code,'-',' Z'),' ',' ')
FROM s_stkmst
WHERE part_code >= 'RKS0516-W'
ORDER BY REPLACE(REPLACE(part_code,'-',' Z'),' ',' ') collate SQL_Latin1_General_Cp1251_CS_AS