请问convert(float,x)类型转换的有关问题_Sql Server

请教convert(float,x)类型转换的问题
select sswx.paihaoid,sswx.name1,sswx.zhi1,convert(float ,sswx.Zhi1)as tt,sswx.name2,sswx.zhi2
from (select sswx1.PaiHaoID as PaiHaoID,sswx1.Name as Name1,sswx1.Zhi as Zhi1,convert(float ,sswx1.Zhi) as dd,sswx2.Name as Name2,sswx2.Zhi as Zhi2
from SuShang_WX_PaiHaoAttribute as sswx1
left join WX_PaiHaoAttribute as sswx2 on sswx1.PaiHaoID=sswx2.PaiHaoID
where
LEN(sswx1.zhi) > 0
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%'
and
sswx1.Name like '%密度%'
and sswx2.Name like '%弹性模量%'
)as sswx
where convert(float,sswx.Zhi1)>111
convert(float,sswx.Zhi1)在上面查询列中可以正常查询出来，但是一加到条件中convert(float,sswx.Zhi1)>111 就报“从数据类型 varchar 转换为 float 时出错。”的错误，搞不懂，求高手指点。
------解决方案--------------------
应该是字段sswx.Zhi1 中非数字，才会大致错误的。
------解决方案--------------------
T-SQL的执行顺序并不一定就是按照你写的方式，它可能在查询过程中又去查找你以为“已经过滤的数据”，前几个月遇到过这样的问题，后来加上判断就好了
------解决方案--------------------

引用:

select sswx1.PaiHaoID as PaiHaoID,sswx1.Name as Name1,sswx1.Zhi as Zhi1,convert(float ,sswx1.Zhi) as dd,sswx2.Name as Name2,sswx2.Zhi as Zhi2
from SuShang_WX_PaiHaoAttribute as sswx1
left join WX_PaiHaoAttribute as sswx2 on sswx1.PaiHaoID=sswx2.PaiHaoID
where
LEN(sswx1.zhi) > 0
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%'
and
sswx1.Name like '%密度%'
and sswx2.Name like '%弹性模量%'

这里面已经把数字都筛选出来了。select sswx.paihaoid,sswx.name1,sswx.zhi1,convert(float ,sswx.Zhi1)as tt,sswx.name2,sswx.zhi2 查询列这里没有问题，但是把convert(float ,sswx.Zhi1)放条件里面就报错了。也就是说：
select sswx.paihaoid,sswx.name1,sswx.zhi1,convert(float ,sswx.Zhi1)as tt,sswx.name2,sswx.zhi2
from (select sswx1.PaiHaoID as PaiHaoID,sswx1.Name as Name1,sswx1.Zhi as Zhi1,convert(float ,sswx1.Zhi) as dd,sswx2.Name as Name2,sswx2.Zhi as Zhi2
from SuShang_WX_PaiHaoAttribute as sswx1
left join WX_PaiHaoAttribute as sswx2 on sswx1.PaiHaoID=sswx2.PaiHaoID
where
LEN(sswx1.zhi) > 0
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%'
and
sswx1.Name like '%密度%'
and sswx2.Name like '%弹性模量%'
)as sswx
这样没有问题，但是
select sswx.paihaoid,sswx.name1,sswx.zhi1,convert(float ,sswx.Zhi1)as tt,sswx.name2,sswx.zhi2
from (select sswx1.PaiHaoID as PaiHaoID,sswx1.Name as Name1,sswx1.Zhi as Zhi1,convert(float ,sswx1.Zhi) as dd,sswx2.Name as Name2,sswx2.Zhi as Zhi2
from SuShang_WX_PaiHaoAttribute as sswx1
left join WX_PaiHaoAttribute as sswx2 on sswx1.PaiHaoID=sswx2.PaiHaoID
where
LEN(sswx1.zhi) > 0
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%'
and
sswx1.Name like '%密度%'
and sswx2.Name like '%弹性模量%'
)as sswx
where convert(float,sswx.Zhi1)>111
这样就有问题了

试试这个：



select sswx.paihaoid,sswx.name1,sswx.zhi1,convert(float ,sswx.Zhi1)as tt,sswx.name2,sswx.zhi2
from (select sswx1.PaiHaoID as PaiHaoID,sswx1.Name as Name1,sswx1.Zhi as Zhi1,convert(float ,sswx1.Zhi) as dd,sswx2.Name as Name2,sswx2.Zhi as Zhi2 
from SuShang_WX_PaiHaoAttribute as sswx1 
left join WX_PaiHaoAttribute as sswx2 on sswx1.PaiHaoID=sswx2.PaiHaoID
where 
LEN(sswx1.zhi) > 0
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%[^0-9.-]%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '%.%.%'
and sswx1.zhi COLLATE Chinese_PRC_BIN NOT LIKE '_%-%' 
and 
sswx1.Name like '%密度%' 
and sswx2.Name like '%弹性模量%'
)as sswx
where  ISNUMERIC(sswx.Zhi1) = 1 and
convert(float,sswx.Zhi1)>111

------解决方案--------------------
你加到哪里？
------解决方案--------------------
问题本质是字段类型设计不合理造成的，应该排查一下错误数据并修正。
如果可以忽略这个问题，就用判断函数判断一下
------解决方案--------------------