server sql 无法从long转为int,如何确定在SQL Server中不能转换为（decimal，float，int）的字段值...

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