系统在查询时报错,具体信息为varchar转换为bigint错误,但是只是部分数据出现问题,说明不是sql语句问题,为了排查是哪行数据出现问题,可以通过以下语句:
SELECT * FROM TABLE WHERE ISNUMERIC(COLUMN)=0 and COLUMN is not null
比如:SELECT top 10 * FROM crm_caseRelation(nolock) WHERE ISNUMERIC(relationId) = 0 and relationId is not null
判断空格:-- 判断空格
select top 10 charindex(' ',cr.RelationID),cr.id,cr.RelationID
from tableName(nolock) cr
where
createTime > '2014-11-03'
and createTime < '2014-11-04'
and charindex(' ',cr.RelationID) > 0
and cr.RelationID is not null