The problem converts column values ​​from VARCHAR (n) to DECIMAL

Question

I have a SQL Server 2000 database with a column of type VARCHAR (255). All data is either NULL or numeric, accurate to two precision points (for example, "11.85"). I tried to run the following T-SQL query but got the error "Error converting varchar to numeric data type"

SELECT CAST([MyColumn] AS DECIMAL)
FROM [MyTable];

I tried a more specific listing, which also failed.

SELECT CAST([MyColumn] AS DECIMAL(6,2))
FROM [MyTable];

I also tried the following to find out if any data is non-numeric and only the return values ​​were NULL.

SELECT ISNUMERIC([MyColumn]), [MyColumn]
FROM [MyTable]
WHERE ISNUMERIC([MyColumn]) = 0;

I tried converting to other data types such as FLOAT and MONEY, but only MONEY was successful. So I tried the following:

SELECT CAST(CAST([MyColumn] AS MONEY) AS DECIMAL)
FROM [MyTable];

... . , ? , , DECIMAL?

!

Answer 1

, , . :

select CAST('3.6' as decimal) -- 3.60
select CAST('3,6' as decimal) -- Error converting data type varchar to numeric.
select CAST('3.6' as money) -- 3.60
select CAST('3,6' as money) -- 36.00 (!)
select ISNUMERIC('3.6') -- 1
select ISNUMERIC('3,6') -- 1

ISNUMERIC :

ISNUMERIC 1, , , ; 0

3.6 3,6 money, isnumeric('3,6') 1.

, ( , ):

select cast(replace('3,6',',','.') as decimal)

- " " Windows. → → → → .

Answer 2

- . , CSV , , .

:

CAST(NULLIF([number_as_a_string],'') AS decimal(13,2))

Answer 3

CAST (isnull (MyColumn, 0) (4,2)) FROM [MyTable];