拍拍尘土

[test]表的按varchar类型字段demo排序的实现
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)