DECLARE @tbl TABLE(StringNumber VARCHAR(10));
INSERT INTO @tbl VALUES
('1-1')
,('1-2')
,('1-11')
,('1-12')
,('1-21');
WITH Parted AS
(
SELECT LEFT(StringNumber,pos.Hyph-1) AS PartOne
,SUBSTRING(StringNumber,pos.Hyph+1,1000) AS PartTwo
,StringNumber
FROM @tbl AS tbl
CROSS APPLY(SELECT CHARINDEX('-',StringNumber) ) AS Pos(Hyph)
)
SELECT * FROM Parted
ORDER BY CAST(PartOne AS INT),CAST(PartTwo AS INT)
1条答案
按热度按时间b4lqfgs41#
试着这样做:
首先,我使用声明的表变量作为测试场景的**模型。
cte将在连字符处剪切数字。然后分别对这两部分使用数字排序。