I have a SQL Server database.
我有一个SQL Server数据库。
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.
我想把VALUE字段转换成decimal,当然是可转换字段。
-
What kind of SQL statement can do this?
什么样的SQL语句可以做到这一点?
-
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 [-+ ()] ?
PS:我认为这可以解决“[a-z]”之类的值,但是我如何添加更多的[-+()]这样的过滤器呢?
1 个解决方案
#1
59
Plain ISNUMERIC is rubbish
平原ISNUMERIC是垃圾
- 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) - 1 -3适用于浮点数,但不适用于十进制数(除非您将浮点数转换为浮点数,然后将其转换为十进制数)
For a particularly cryptic but failsafe solution, append e0
or .0e0
then use ISNUMERIC
对于一个特别神秘但故障安全的解决方案,附加e0或.0e0,然后使用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
#1
59
Plain ISNUMERIC is rubbish
平原ISNUMERIC是垃圾
- 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) - 1 -3适用于浮点数,但不适用于十进制数(除非您将浮点数转换为浮点数,然后将其转换为十进制数)
For a particularly cryptic but failsafe solution, append e0
or .0e0
then use ISNUMERIC
对于一个特别神秘但故障安全的解决方案,附加e0或.0e0,然后使用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