If a field in the database is named Bedno, the type is Nvchar, which has {1,2,3,11,12,23, plus 2, plus 3} data.
At this point I need to sort these data, the numbers by the size of the front, the Chinese characters in the first word pinyin first letter in the order of the following, that needs 1,2,3,11,12,23, plus 2, plus 3 such results.
Usually ordered by Bedno, the result is: 1,11,12,2,23,3, plus 2, plus 3, can not get the order required.
If you use ORDER by cast (bedno as int), the data such as 2 plus 3 will be error-able, because it cannot be converted to an int type.
With ORDER by LEN (Bedno), you can get the result: 1,2,3,11,12,23, plus 2, plus 3, to meet my needs.
It should be noted at this point that if there are other fields that need to be sorted after order by Len (Bedno), for example: ORDER by Len (Bedno), field A, field B, then the resulting result is probably not the desired order.
The workaround is also very simple, just need to do a sort of bedno after Len (Bedno). For example: ORDER by LEN (Bedno), Bedno, field A, field B, and so on, can get the ideal sort result.
SQL Server R2 to sort fields that contain both numbers and Chinese