SQL Server: error converting nvarchar to numeric data type

Question

If I run the SQL query below; I get the following error:

Error converting nvarchar to numeric data type.

COLUMNA contains only numbers (negative and positive), including fields with a maximum length of up to two digits after the decimal and stored as decimal numbers.

 IF OBJECT_ID('st00_TABLEA','U') IS NOT NULL DROP TABLE [st00_TABLEA] SELECT COLUMNA AS COLUMNA_s ,CASE WHEN [COLUMNA] = '' THEN 0 ELSE CONVERT(NUMERIC(18,2),REPLACE([COLUMNA],',','.')) END AS COLUMNA INTO st00_TABLEA FROM dbosu.TABLEA; 

I also tried the following, but still the same problem:

 IF OBJECT_ID('st00_TABLEA','U') IS NOT NULL DROP TABLE [st00_TABLEA] SELECT COLUMNA AS COLUMNA_s ,CONVERT(DECIMAL(18,2),COLUMNA) AS COLUMNA INTO st00_TABLEA FROM dbosu.TABLEA; 

Answer 1

You may need to review the data in the column, but in any case, you can do one of the following: -

1- check if it is numeric and then convert it, add another value like 0

 Select COLUMNA AS COLUMNA_s, CASE WHEN Isnumeric(COLUMNA) = 1 THEN CONVERT(DECIMAL(18,2),COLUMNA) ELSE 0 END AS COLUMNA 

2- select only numerical values ​​from the column

 SELECT COLUMNA AS COLUMNA_s ,CONVERT(DECIMAL(18,2),COLUMNA) AS COLUMNA where Isnumeric(COLUMNA) = 1