SQL Server 2000 implements a case of sorting by functions similar to the VB Val Function

SQL Server 2000 implements a case of sorting by functions similar to the VB Val Function

Recently, the following data was sorted during project maintenance:

Before sorting:

Class photo name
Mechanical Class 1, Zhang san.jpg
Mechanical Class 1 7Li si.jpg
Mechanical Class 1 3wangwu.jpg
Accounting class 2 3ru. jpg
Accounting class 2, 6 pictures of flowers .jpg
Accounting class 2, 1zhenghua .jpg
Class 1, sports, 3 iron male .jpg
Sports Class 1 male 1. jpg
Accounting Class 2: 10ruohu.jpg
Sports Class 1 30E male .jpg

After sorting (result to be implemented ):

Class photo name
Accounting class 2, 1zhenghua .jpg
Accounting class 2 3ru. jpg
Accounting class 2, 6 pictures of flowers .jpg
Accounting Class 2: 10ruohu.jpg
Mechanical Class 1, Zhang san.jpg
Mechanical Class 1 3wangwu.jpg
Mechanical Class 1 7Li si.jpg
Sports Class 1 male 1. jpg
Class 1, sports, 3 iron male .jpg
Sports Class 1 30E male .jpg

Some people may say that this is not simple, as long as you enter the "select class, photo name from [Table] Order by class, photo name" statement, no result is returned? Is this true? Please refer to the results after the SQL statement is executed:

Class photo name
Accounting Class 2: 10ruohu.jpg
Accounting class 2, 1zhenghua .jpg
Accounting class 2 3ru. jpg
Accounting class 2, 6 pictures of flowers .jpg
Mechanical Class 1, Zhang san.jpg
Mechanical Class 1 3wangwu.jpg
Mechanical Class 1 7Li si.jpg
Sports Class 1 male 1. jpg
Sports Class 1 30E male .jpg
Class 1, sports, 3 iron male .jpg

Is it very different from the expected results? How can we solve this problem and achieve the desired results? Since SQL server does not have a Val function similar to VB, it is difficult to sort the above data. However, you only need to execute the following SQL statement, and we will be given free of charge today. The code is highlighted as follows:

Select class, photo name
From (select *, case when (Unicode (substring (photo, 1, 1) between 48 and
57) and not (Unicode (substring (photo, 2, 1) between 48 and 57)
Then substring (photo, 1, 1) When (Unicode (substring (photo, 1, 1 ))
Between 48 and 57) and (Unicode (substring (photo, 2, 1)
48 and 57) Then substring (photo, 1, 2) end as photoid
From (select *, ltrim (photo name) as photo
From [Table]) L) LL
Order by class, cast (isnull (photoid, 0) as INT)

I did not expect a simple function to write such complicated code. It seems that the previous "wheel building" was not complete, it will bring great troubles to the later application developers.