demo字段
---------------
1LP1
1LP2
2LP1
3LP3
19LP23
23LP1
首先显示测试
---------------------------------
SELECT CAST(LEFT(demo, CHARINDEX('LP', demo) - 1) AS int) AS Expr1,
CAST(RIGHT(demo, LEN(demo) - CHARINDEX('LP', demo) - 1) AS int) AS Expr2
FROM test
结论
-----------------------------------
SELECT *
FROM test
ORDER BY
CAST(LEFT(demo, CHARINDEX('LP', demo) - 1) AS int),
CAST(RIGHT(demo, LEN(demo) - CHARINDEX('LP', demo) - 1) AS int)
