新问题！如何在text字段中按照数字大小排序

[jsidiot]

Item No. Diameter (mm) Segment Size (mm) Number of Segment Inner Hole（ mm)
1200 7.8-8.3*10 80 80/100
1600 9.0-9.5*11 108 80/100
250 2.4*4+1 18 25.4/50
300 3.0*5+1 21 25.4/50
350 3.0*5+1 24 25.4/50
400 3.2-3.5*5+1 28 25.4/50
450 3.5-3.8*5+1 36 25.4/50
500 3.5-3.8*5+1 38 25.4/50
700 5.0-5.2*6+1 50 50/80

如何利用上面的每一拦进行排序呢？按照数字的大小？上面的都是text类型的

[jsidiot]

select * from t_product_data where productid= '65' and del_sign=0 order by cast(substring(field_2,1,patindex('%[^0123456789]%',field_2)-1) as numeric) desc

向 substring 函数传递了无效的 length 参数。

[yoki]

select * from t_product_data where productid= '65' and del_sign=0 order by cast(substring(field_2,1,isnull(patindex('%[^0123456789]%',field_2),1)-1) as numeric) desc

[yoki]

sorry:
This is true!

select * from t_product_data where productid= '65' and del_sign=0 order by
cast(substring(field_2,1,case when PATINDEX( '%[^0123456789]%',field_2)=0 then datalength(field_2) else PATINDEX( '%[^0123456789]%',field_2)-1 end) as numeric)
desc

[jsidiot]

我顶一下
各位老大帮忙

[CrazyFor]

order by substrint(textCol,1,8000)

对text字段的前8000个字符来排序。

[jsidiot]

谢谢，我的意思你可能没有理解
比如说现在有
200x
3000x
4x
那么怎么才能按照4->200->3000 的顺序排序呢
你说的那样只能按照字符的unicode来排序
不能动态的按照数字来排序

[yoki]

declare @ table (a text)
insert into @ values ('200x')
insert into @ values ('3000x')
insert into @ values ('4x')
select * from @
order by cast(substring(a,1,PATINDEX( '%[^0123456789]%',a)-1) as int)

[yoki]

select *
from 你的表
order by cast(substring(排序字段,1,PATINDEX( '%[^0123456789]%',排序字段)-1) as numeric)