数字型的字符串字段排序问题

[qufusongyu]

有一个字段为编号
内容大体如下:


1-1
1-2
......
1-50
....
50-5
50-6....
Select * from 表 order by 编号
排序不是按照我的意图来排的.
1和11都是挨着的了.
如果按照数字大小来排列呢

[--小F--]

order by left(编号,charindex('-',编号)+1)

[sgtzzc]

order by cast(left(编号,charindex('-',编号)-1) as int)

[快乐_石头]

select * from tb order by cast(left(编号,charindex('-',编号)-1)as int)

[--小F--]

---2楼正确 我掉了转换的一步
order by cast(left(编号,charindex('-',编号)-1) as int)

[sgtzzc]

order by 
  cast(left(编号,charindex('-',编号)-1) as int),
  cast(right(编号,len(编号)-charindex('-',编号)) as int)

[luoyoumou]

Select *  from 表 order by substring(1,charindex('-',编号)-1) asc,
                           substring(charindex('-',编号)+1,len(编号));

[fwacky]

order by left(编号,charindex('-',编号)-1)

[qufusongyu]

这个字段里面如果含有Null值和空字符的话,这个语句会出错

[SQL77]

[Quote=引用 8 楼 qufusongyu 的回复:]
这个字段里面如果含有Null值和空字符的话,这个语句会出错
[/Quote]
用CASE WHEN
Select * from 表 order by CASE WHEN 编号 IS NULL THEN 0 ELSE
substring(1,charindex('-',编号)-1) END asc