SQL Server:将数据类型 nvarchar 转换为数字时出错 [英] SQL Server: Error converting data type nvarchar to numeric

问题描述

如果我运行下面的 SQL 查询；我收到以下错误:

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

将数据类型 nvarchar 转换为数字时出错.

Error converting data type nvarchar to numeric.

COLUMNA 只包含数字(负数和正数)，包括小数点后最多两位的字段，并存储为点十进制.

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;

推荐答案

您可能需要修改列中的数据，但无论如何您都可以执行以下操作之一:-

You might need to revise the data in the column, but anyway you can do one of the following:-

1-检查它是否是数字然后将其转换为其他值，例如 0

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- 仅从列中选择数值

2- select only numeric values from the column

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

这篇关于SQL Server:将数据类型 nvarchar 转换为数字时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！