SQL Server: Error converting data type nvarchar to numeric

Question

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

Error converting data type nvarchar to numeric.

COLUMNA contains only numbers (negative and positive) including fields with maximal up to two digits after the decimal and is stored as dot decimal.

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 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 might need to revise the data in the column, but anyway you can do one of the following:-

1- check if it is numeric then convert it else put 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 numeric values from the column

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