For a long time, the Sorting Problem of string-type data containing numbers in the database has been bothering me.
For example, the data in the storecode field is:
SCM-FA-ZY-HC1
SCM-FA-ZY-HC2
SCM-FA-ZY-HC3
SCM-FA-ZY-HC4
SCM-FA-ZY-HC5
SCM-FA-ZY-HC6
SCM-FA-ZY-HC7
SCM-FA-ZY-HC8
SCM-FA-ZY-HC9
SCM-FA-ZY-HC10
SCM-FA-ZY-HC11
SCM-FA-ZY-HC12
SCM-FA-ZY-HC13
SCM-FA-ZY-HC14
SCM-FA-ZY-HC15
SCM-FA-ZY-HC16
SCM-FA-ZY-HC17
SCM-FA-ZY-HC18
SCM-FA-ZY-HC19
SCM-FA-ZY-HC20
SCM-FA-ZY-HC21
Directly write order by storecode in the query statement. The result is:
SCM-FA-ZY-HC1
SCM-FA-ZY-HC10
SCM-FA-ZY-HC11
SCM-FA-ZY-HC12
SCM-FA-ZY-HC13
SCM-FA-ZY-HC14
SCM-FA-ZY-HC15
SCM-FA-ZY-HC16
SCM-FA-ZY-HC17
SCM-FA-ZY-HC18
SCM-FA-ZY-HC19
SCM-FA-ZY-HC2
SCM-FA-ZY-HC20
SCM-FA-ZY-HC21
This result naturally does not meet the requirements. What should I do?
Today, I finally found the solution-Add the following function conversion when order:
Order by right (colname, patindex ('% [0-9] %', colname ))
It is quite easy to use and solves another "hate" problem.