如何确定在SQL Server中不能转换为(decimal,float,int)的字段值 [英] How to determine the field value which can not convert to (decimal, float,int) in SQL Server
问题描述
我有一个SQL Server数据库。
一个字段的值类似于
ID VALUE
1 NEGATIF
2 11.4
3 0.2
4 A RH(+)
5 -----
6>>>
7 5.6<
8 -13.9
我想将CONVERT VALUE字段转换为十进制,
我如何理解在转换时出现错误的值是什么?
WHERE VALUE LIKE'[az]'
但如何添加更多过滤器像[ - +()]?
普通ISNUMERIC是垃圾
- 空字符串
+
,-
和 - 也就是
+。
等 -
1e -3
对于浮点型而不是小数型有效(除非您将CAST浮动为十进制)
一个特别隐秘但失效的解决方案,附加 e0
或 .0e0
use ISNUMERIC
SELECT
ISNUMERIC(MyCOl +'e0') - 十进制检查,
ISNUMERIC(MyCOl +'。 0e0') - 整数检查
所以
SELECT
ID,VALUE,
CAST(
CASN WHEN ISNUMERIC(VALUE +'e0')= 1 THEN VALUE ELSE NULL END
AS decimal(38,10)
)AS ConvertedVALUE
FROM
Mytable
I have a SQL Server database.
One field has values which are like
ID VALUE
1 NEGATIF
2 11.4
3 0.2
4 A RH(+)
5 -----
6 >>>>>
7 5.6<
8 -13.9
I want to CONVERT VALUE field to decimal, of course convert-able fields.
What kind of SQL statement can do this?
How can I understand which value is raising error while converting?
PS: I think this can solve WHERE VALUE LIKE '[a-z]'
but how can I add more filter like [-+ ()] ?
Plain ISNUMERIC is rubbish
- Empty string,
+
,-
and. are all valid
- So is
+.
etc 1e-3
is valid for float but not decimal (unless you CAST to float then to decimal)
For a particularly cryptic but failsafe solution, append e0
or .0e0
then use ISNUMERIC
SELECT
ISNUMERIC(MyCOl + 'e0') --decimal check,
ISNUMERIC(MyCOl + '.0e0') --integer check
So
SELECT
ID, VALUE,
CAST(
CASE WHEN ISNUMERIC(VALUE + 'e0') = 1 THEN VALUE ELSE NULL END
AS decimal(38, 10)
) AS ConvertedVALUE
FROM
Mytable
这篇关于如何确定在SQL Server中不能转换为(decimal,float,int)的字段值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!