SQL中去除某列的非数字字符问题
有这么一个SQL:
select A.projectname,A.projectid,A.Xi,B.CLx,B.UCLx,B.LCLx from
(select projectname,value as Xi,projectid from TBLPAS_PRJ_TARGET where type = '末轮测试缺陷密度(个/KLOC)'
and charindex(','+rtrim(projectid)+',',','+'2008001,2008002'+',')>0) as A,
(select
avg (CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) as CLx,
avg(CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) + stdev(CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) as UCLx,
avg(CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) - stdev(CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) as LCLx
from TBLPAS_PRJ_TARGET
where type = '末轮测试缺陷密度(个/KLOC)'
and charindex(','+rtrim(projectid)+',',','+'2008001,2008002'+',')>0) as B
我的数据库中有的字段的值有%如45.7%,有的没有如:45.7
而SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1)
中的PATINDEX用来查找非数字的字符在value这列的第几位,如果这个时候有%的话都一切正常,而如果没%PATINDEX就找不到则返回0在减去1就等于-1这个时候在传给sbustring就会产生向 substring 函数传递了无效的 length 参数。
出现这中情况该怎么处理呢?