同事运行一段sql报错,显示:无法从varcahr转化为numeric。此字段是varchar型。
--1. 报错:无法从varcahr转化为numeric
SELECT sum(CAST(jg AS NUMERIC(10,2)))
FROM RPT_DIST_RULE
--2. 还是会报错
SELECT sum(CAST(jg AS NUMERIC(10,2)))
FROM RPT_DIST_RULE
WHERE isnumeric(jg) = 1 --只过滤数值
--3. 没有返回记录,说明jg字段中不包含空格
SELECT sum(CAST(jg AS NUMERIC(10,2)))
FROM RPT_DIST_RULE
WHERE jg LIKE '% %' --等于空格
--4. 计算此字段的长度
SELECT jg,
len(jg)
FROM RPT_DIST_RULE
--4.2 进一步通过去重,缩小数据条数,这样更容易发现错误数据
-- 按照此字段的长度从小到大排序,发现有的字符串为'.'或'.5'
SELECT DISTINCT
jg,
len(jg)
FROM RPT_DIST_RULE
ORDER BY 2
--5.1 验证了确实当字符串为'.'时,进行转化会报错
SELECT CAST(a AS NUMERIC(10,2))
from
(
SELECT '.' a
)a
--5.2 而当字符串为'.5'时,转化不会报错
SELECT CAST(a AS NUMERIC(10,2))
from
(
SELECT '.5' a
)a
--5.3 另外,在用isnumeric函数判断一个字符串是否为数值时,都会返回1,也就是都是数值
SELECT a,
isnumeric(a)
from
(
SELECT '.5' a
UNION ALL
SELECT '.' a
)a
--6. 本质上,这种错误是由于客户端没有控制好导致的,客户端可以通过正则表达式来限制输入字符
-- 对于本例,直接通过把'.'更新为'0'来解决。
UPDATE RPT_DIST_RULE
SET jg = '0'
WHERE jg = '.'